Burning Glass Queries

burning_glass_queries.org

SUPPORT-2027-burning-glass-queries

final index attempt

CREATE INDEX CONCURRENTLY index_burning_glass_jobs_full_search
ON burning_glass_jobs
USING GIN ((
    setweight(
      to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')
     ), 'A')
     || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, '')
   )
));

explain SELECT COUNT(*) FROM "burning_glass_jobs" INNER JOIN (SELECT "burning_glass_jobs"."id" AS pg_search_id, (ts_rank((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))), (to_tsquery('simple', ''' ' || 'business' || ' ''' || ':*')), 0)) AS rank FROM "burning_glass_jobs" WHERE (((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'business' || ' ''' || ':*'))))) AS pg_search_9049415c020507460935a8 ON "burning_glass_jobs"."id" = pg_search_9049415c020507460935a8.pg_search_id WHERE "burning_glass_jobs"."internship" = 'false';

--   Finalize Aggregate  (cost=198013.33..198013.34 rows=1 width=8)
--   ->  Gather  (cost=198012.91..198013.32 rows=4 width=8)
--         Workers Planned: 4
--         ->  Partial Aggregate  (cost=197012.91..197012.92 rows=1 width=8)                                                            ->  Nested Loop  (cost=1062.33..196932.84 rows=32027 width=0)
--                     ->  Parallel Bitmap Heap Scan on burning_glass_jobs burning_glass_jobs_1  (cost=1061.89..142994.58 rows=32196 width=8)                                                                                                                              Recheck Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE((title)::text, ''::text)), 'A'::"char") || to_tsvector('simple'::regconfig, COALESCE((description)::text, ''::text))) @@ '''business'':*'::tsquery)
--                           ->  Bitmap Index Scan on index_burning_glass_jobs_full_search  (cost=0.00..1029.70 rows=128786 width=0)                                                                                                                                             Index Cond: ((setweight(to_tsvector('simple'::regconfig, COALESCE((title)::text, ''::text)), 'A'::"char") || to_tsvector('simple'::regconfig, COALESCE((description)::text, ''::text))) @@ '''business'':*'::tsquery)
--                     ->  Index Scan using burning_glass_jobs_pkey on burning_glass_jobs  (cost=0.43..1.68 rows=1 width=8)
--                           Index Cond: (id = burning_glass_jobs_1.id)
--                           Filter: (NOT internship)
--(12 rows)

It’s doing an index scan, but it still timed out in staging.

triage

• user 14860001
• https://studentplaybook.benchprep.com/account/user-portal/internships

curl ‘https://studentplaybook.staging.benchprep.com/api/v2/user_manager/jobs/search?search_text=business&city=&state=&post_date=&include_all_locations=false&internship=false&per_page=10&country=USA&auth_token=X

ssh deploy@10.176.230.78

SELECT COUNT(*) FROM "burning_glass_jobs" INNER JOIN (
  SELECT "burning_glass_jobs"."id" AS pg_search_id,
  (
  ts_rank((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A')
  || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))), (to_tsquery('simple', ''' ' || 'information' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'technology' || ' ''' || ':*')), 0))


AS rank FROM "burning_glass_jobs" WHERE (((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'information' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'technology' || ' ''' || ':*'))))) AS pg_search_9049415c020507460935a8 ON "burning_glass_jobs"."id" = pg_search_9049415c020507460935a8.pg_search_id WHERE "burning_glass_jobs"."internship" = 'false'

  SELECT "burning_glass_jobs".*
  FROM "burning_glass_jobs"
  WHERE (((to_tsvector('english', coalesce("burning_glass_jobs"."description"::text, ''))) @@ (to_tsquery('english', ''' ' || 'business' || ' ''' || ':*'))))
  ORDER BY "burning_glass_jobs"."id" ASC;

select (setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, '')))
from burning_glass_jobs limit 2;

CREATE INDEX burning_glass_jobs_description_search
ON burning_glass_jobs
USING GIN (to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, '')));

CREATE INDEX burning_glass_jobs_title_search
ON burning_glass_jobs
USING GIN (to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A');

• no blocks

wmx_rails_api_staging=# SELECT COUNT(*) FROM “burning_glass_jobs”; count ───────── 6273040 (1 row)

Time: 993.082 ms

• when did this start?
  • https://one.newrelic.com/launcher/nr1-core.explorer?platform[timeRange][duration]=10800000&pane=eyJuZXJkbGV0SWQiOiJhcG0tbmVyZGxldHMudHJhbnNhY3Rpb25zIiwiZW50aXR5R3VpZCI6Ik5EWTBNVEY4UVZCTmZFRlFVRXhKUTBGVVNVOU9mREV3TmpreE56ZyIsInNvcnQiOiJhdmVyYWdlIiwic2VsZWN0ZWRTZXJpZXMiOiJkMjc3ZGQ2ZTE3N2VlNGM5YTZhMzE5NGUwY2MxYTMzNTMwNzFkMjViIiwiZHJpbGxkb3duIjp7InRyYW5zYWN0aW9uTmFtZSI6IkNvbnRyb2xsZXIvdXNlcl9tYW5hZ2VyL2pvYnMvc2VhcmNoIn19&sidebars[0]=eyJuZXJkbGV0SWQiOiJucjEtY29yZS5hY3Rpb25zIiwic2VsZWN0ZWROZXJkbGV0Ijp7Im5lcmRsZXRJZCI6ImFwbS1uZXJkbGV0cy50cmFuc2FjdGlvbnMifSwiZW50aXR5R3VpZCI6Ik5EWTBNVEY4UVZCTmZFRlFVRXhKUTBGVVNVOU9mREV3TmpreE56ZyJ9
  • in staging around 1:30 pm today
• did an import job blow up the table?
  • no, looks like it’s been happening for a while
• are we missing an index?

SELECT COUNT(*) FROM "burning_glass_jobs" INNER JOIN (
  SELECT "burning_glass_jobs"."id" AS pg_search_id,
  (
  ts_rank((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A')
  || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))), (to_tsquery('simple', ''' ' || 'information' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'technology' || ' ''' || ':*')), 0))


AS rank FROM "burning_glass_jobs" WHERE (((setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'information' || ' ''' || ':*') && to_tsquery('simple', ''' ' || 'technology' || ' ''' || ':*'))))) AS pg_search_9049415c020507460935a8 ON "burning_glass_jobs"."id" = pg_search_9049415c020507460935a8.pg_search_id WHERE "burning_glass_jobs"."internship" = 'false'

  SELECT "burning_glass_jobs".*
  FROM "burning_glass_jobs"
  WHERE (((to_tsvector('english', coalesce("burning_glass_jobs"."description"::text, ''))) @@ (to_tsquery('english', ''' ' || 'business' || ' ''' || ':*'))))
  ORDER BY "burning_glass_jobs"."id" ASC;

select (setweight(to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A') || to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, '')))
from burning_glass_jobs limit 2;

CREATE INDEX burning_glass_jobs_description_search
ON burning_glass_jobs
USING GIN (to_tsvector('simple', coalesce("burning_glass_jobs"."description"::text, '')));

CREATE INDEX burning_glass_jobs_title_search
ON burning_glass_jobs
USING GIN (to_tsvector('simple', coalesce("burning_glass_jobs"."title"::text, '')), 'A');