explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VEUh

Settings
# exclusive inclusive rows x rows loops node
1. 5,696.277 14,526.311 ↓ 4,332.0 4,332 1

Nested Loop Left Join (cost=3,775.29..3,864.61 rows=1 width=309) (actual time=172.738..14,526.311 rows=4,332 loops=1)

  • Join Filter: ((student_resumes.created_at < career_fair_interactions.created_at) AND (students.id = student_resumes.student_id))
  • Rows Removed by Join Filter: 12,313,185
2.          

CTE career_fair_interactions

3. 15.140 86.927 ↓ 345.1 4,486 1

HashAggregate (cost=3,337.13..3,337.26 rows=13 width=181) (actual time=84.855..86.927 rows=4,486 loops=1)

  • Group Key: organization_interactions.id, organization_interactions.student_id, organization_interactions.organization_id, organization_interactions.created_at, organization_interactions.updated_at, organization_interactions.ye_event_id, organization_interactions.ye_event_name, organization_interactions.organization_interaction_source_id, ('Krannert/PAA Public Accounting Fair'::text), ('America/Indiana/Indianapolis'::text), event_interactions.is_going
4. 0.496 71.787 ↓ 345.1 4,486 1

Append (cost=0.28..3,336.78 rows=13 width=181) (actual time=0.034..71.787 rows=4,486 loops=1)

5. 0.129 6.840 ↓ 135.0 135 1

Nested Loop Left Join (cost=0.28..389.16 rows=1 width=194) (actual time=0.034..6.840 rows=135 loops=1)

6. 6.441 6.441 ↓ 135.0 135 1

Seq Scan on organization_interactions (cost=0.00..380.60 rows=1 width=129) (actual time=0.019..6.441 rows=135 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-05 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-06 04:00:00+00'::timestamp with time zone) AND (btrim((ye_event_name)::text) = ANY ('{"Purdue - Krannert/ PAA Public Accounting Career Fair","Purdue Public Accounting Career Fair - 9/5","Purdue Krannert Public Accounting Fair 2019","Krannert/PAA Public Accounting CF","Purdue Accounting Career Fair","Purdue: Krannert Public Accounting Fair 2019","Purdue Krannert/PAA Public Accounting CF","Purdue University - Krannert/PAA Public Accounting CF"}'::text[])))
  • Rows Removed by Filter: 5,625
7. 0.270 0.270 ↓ 0.0 0 135

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions (cost=0.28..8.30 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=135)

  • Index Cond: ((event_id = '6bcb0258-b9c0-4a77-b547-5ae485f532d0'::uuid) AND (student_id = organization_interactions.student_id))
8. 0.748 9.250 ↓ 260.0 780 1

Nested Loop Left Join (cost=0.28..521.60 rows=3 width=194) (actual time=0.086..9.250 rows=780 loops=1)

9. 6.942 6.942 ↓ 260.0 780 1

Seq Scan on organization_interactions organization_interactions_1 (cost=0.00..496.66 rows=3 width=129) (actual time=0.072..6.942 rows=780 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-12 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-13 04:00:00+00'::timestamp with time zone) AND (btrim((ye_event_name)::text) = ANY ('{"Purdue: Krannert/SMEF Business Fall Fair 2019","Purdue - SMEF Fall Career Fair 2019","Purdue University - Krannert/SMEF Business Fall Fair 2019","SMEF Purdue School of Management Fall2019","Purdue Krannert SMEF & Next Day Interviews","Purdue - SMEF Business Career Fair","Purdue Campus Team - Career Fair","Purdue SMEF Fall 2019","SMEF Career Fair","Krannert/SMEF Undergraduate Business CF","Purdue University - Krannert/SMEF Undergraduate Business Career Fair","2019 Purdue SMEF Career Fair","Purdue - Krannert/SMEF Fall Career Fair","Purdue Krannert SMEF Fall Fair","2019 Fall Krannert SMEF Fair Purdue University (Crane)","Krannert SMEF Career Fair","Purdue Krannert/SMEF Business Fall Fair 2019","Krannert/SMEF Undergraduate Business CF ","Purdue - SMEF Fall Career Fair","Purdue Business Career Fair","Purdue Career Fair","Purdue University","Purdue SMEF 2019 Career Fair","Krannert/SMEF Undergraduate Business Career Fair"}'::text[])))
  • Rows Removed by Filter: 4,980
10. 1.560 1.560 ↓ 0.0 0 780

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_1 (cost=0.28..8.30 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=780)

  • Index Cond: ((event_id = 'aace5f2d-531c-45ea-b276-b8ded7b17d64'::uuid) AND (student_id = organization_interactions_1.student_id))
11. 0.334 13.745 ↓ 435.0 870 1

Nested Loop Left Join (cost=0.28..462.50 rows=2 width=194) (actual time=0.348..13.745 rows=870 loops=1)

12. 2.971 2.971 ↓ 435.0 870 1

Seq Scan on organization_interactions organization_interactions_2 (cost=0.00..445.88 rows=2 width=129) (actual time=0.335..2.971 rows=870 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-18 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-19 04:00:00+00'::timestamp with time zone) AND (btrim((ye_event_name)::text) = ANY ('{"University of South Carolina - Moore School of Business Expo","Moore School of Business Career Fair at USC","USC Career Fair","Moore School of Business Fall 2019 EXPO","Fall 2019 University of South Carolina - Darla Moore Career Fair","USC Darla Moore School of Business Fall 2019 EXPO","Collins Aerospace-University of South Carolina","Collins Aerospace- University of South Carolina Information Session","2019 September - Global Supply Chain - Human Resources - University of South Carolina Darla Moore School Fall 2019 Business Expo - SC - United States","Darla Moore School of Business Career Fair","USC Moore School of Business Fall 2019 EXPO","University of South Carolina Moore school of business fall 2019 expo","U South Carolina - Moore School of Business Fall 2019 Expo","Darla Moore School of Business Expo- Fall 2019","University of South Carolina Moore School of Business Fall EXPO","Univ of South Carolina - Moore School of Business Expo","University of South Carolina - Darla Moore School of Business Expo"}'::text[])))
  • Rows Removed by Filter: 4,890
13. 10.440 10.440 ↑ 1.0 1 870

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_2 (cost=0.28..8.30 rows=1 width=17) (actual time=0.012..0.012 rows=1 loops=870)

  • Index Cond: ((event_id = '1c54cf21-6716-4b6b-9aec-27350faa729d'::uuid) AND (student_id = organization_interactions_2.student_id))
14. 4.953 11.267 ↓ 402.0 804 1

Nested Loop Left Join (cost=0.28..448.00 rows=2 width=194) (actual time=0.673..11.267 rows=804 loops=1)

15. 4.706 4.706 ↓ 402.0 804 1

Seq Scan on organization_interactions organization_interactions_3 (cost=0.00..431.37 rows=2 width=129) (actual time=0.662..4.706 rows=804 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-20 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-21 04:00:00+00'::timestamp with time zone) AND (btrim((ye_event_name)::text) = ANY ('{"Univ of Maryland","University of Maryland Smith Career Fair, Fall 2019","Maryland Career Fair","2019_Fall_Atlantic_University of Maryland_Career Fair","University of Maryland - Smith Undergraduate Business Career Fair","UMD UG Smith Career Fair 2019","University of Maryland - Smith Undergraduate Career Fair - Business","UMCP Smith Business School Fall Career Fair","Smith School at USG- Employer of the Day","Smith School Fall Career Fair","University of Maryland","Smith Career Fair","University of Maryland - Smith School of Business - Smith Undergraduate Career Fair | Fall 2019","UMD Smith COB Career Fair","UMD Smith Career Fair"}'::text[])))
  • Rows Removed by Filter: 4,956
16. 1.608 1.608 ↑ 1.0 1 804

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_3 (cost=0.28..8.30 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=804)

  • Index Cond: ((event_id = 'bb412c92-7b51-4c01-9a32-0c750781bd6d'::uuid) AND (student_id = organization_interactions_3.student_id))
17. 0.056 7.504 ↓ 84.0 84 1

Nested Loop Left Join (cost=0.28..345.78 rows=1 width=194) (actual time=5.708..7.504 rows=84 loops=1)

18. 3.248 3.248 ↓ 84.0 84 1

Seq Scan on organization_interactions organization_interactions_4 (cost=0.00..337.08 rows=1 width=129) (actual time=1.747..3.248 rows=84 loops=1)

  • Filter: ((btrim((ye_event_name)::text) = ANY ('{"Government Career Fair - Yale University","Government Career Fair 2019 (Yale University)"}'::text[])) AND (timezone('UTC'::text, created_at) > '2019-09-19 20:00:00'::timestamp without time zone) AND (timezone('UTC'::text, created_at) < '2019-09-20 20:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 5,676
19. 4.200 4.200 ↑ 1.0 1 84

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_4 (cost=0.28..8.30 rows=1 width=17) (actual time=0.050..0.050 rows=1 loops=84)

  • Index Cond: ((event_id = 'f9240637-e421-4844-a36e-999fd7c4f96d'::uuid) AND (student_id = organization_interactions_4.student_id))
20. 1.296 6.364 ↓ 1,233.0 1,233 1

Nested Loop Left Join (cost=0.28..352.67 rows=1 width=194) (actual time=0.965..6.364 rows=1,233 loops=1)

21. 2.602 2.602 ↓ 1,233.0 1,233 1

Seq Scan on organization_interactions organization_interactions_5 (cost=0.00..344.33 rows=1 width=129) (actual time=0.950..2.602 rows=1,233 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-24 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-25 04:00:00+00'::timestamp with time zone) AND (btrim((ye_event_name)::text) = ANY ('{"STEM Career Fair 2019 (Yale University)","ENG Yale STEM Networking Fair - Fall 2019","Yale STEM Career Fair"}'::text[])))
  • Rows Removed by Filter: 4,527
22. 2.466 2.466 ↑ 1.0 1 1,233

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_5 (cost=0.28..8.30 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=1,233)

  • Index Cond: ((event_id = '31cc255e-d6a2-4fe9-aed1-2688721ec812'::uuid) AND (student_id = organization_interactions_5.student_id))
23. 0.183 8.634 ↓ 317.0 317 1

Nested Loop Left Join (cost=0.28..374.61 rows=1 width=194) (actual time=1.353..8.634 rows=317 loops=1)

24. 3.696 3.696 ↓ 317.0 317 1

Seq Scan on organization_interactions organization_interactions_6 (cost=0.00..366.09 rows=1 width=129) (actual time=1.341..3.696 rows=317 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-26 05:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-28 05:00:00+00'::timestamp with time zone) AND ((btrim((ye_event_name)::text) ~~* '%nbmbaa%'::text) OR (btrim((ye_event_name)::text) ~~* '%national black%'::text)))
  • Rows Removed by Filter: 5,443
25. 4.755 4.755 ↓ 0.0 0 317

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_6 (cost=0.28..8.30 rows=1 width=17) (actual time=0.015..0.015 rows=0 loops=317)

  • Index Cond: ((event_id = '12111fc7-19d3-4e4f-8c24-fdde33ee1f2c'::uuid) AND (student_id = organization_interactions_6.student_id))
26. 0.218 7.687 ↓ 131.5 263 1

Nested Loop Left Join (cost=0.28..442.32 rows=2 width=194) (actual time=5.543..7.687 rows=263 loops=1)

27. 6.943 6.943 ↓ 131.5 263 1

Seq Scan on organization_interactions organization_interactions_7 (cost=0.00..424.12 rows=2 width=129) (actual time=5.527..6.943 rows=263 loops=1)

  • Filter: ((timezone('UTC'::text, created_at) > '2019-09-27 04:00:00+00'::timestamp with time zone) AND (timezone('UTC'::text, created_at) < '2019-09-28 04:00:00+00'::timestamp with time zone) AND ((btrim((ye_event_name)::text) ~~* '%construction management %'::text) OR (btrim((ye_event_name)::text) ~~* '%SCMT%'::text) OR (btrim((ye_event_name)::text) ~~* '%BCM%'::text) OR (btrim((ye_event_name)::text) ~~* '%CMT%'::text)))
  • Rows Removed by Filter: 5,497
28. 0.526 0.526 ↓ 0.0 0 263

Index Scan using index_event_interactions_on_event_id_and_student_id on event_interactions event_interactions_7 (cost=0.28..8.30 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=263)

  • Index Cond: ((event_id = '6182e947-bfce-40cb-bcaf-f58b1a928578'::uuid) AND (student_id = organization_interactions_7.student_id))
29.          

CTE career_fair_student_journeys

30. 14.801 43.580 ↓ 178.5 2,320 1

GroupAggregate (cost=25.51..25.83 rows=13 width=88) (actual time=28.291..43.580 rows=2,320 loops=1)

  • Group Key: career_fair_interactions_1.student_id, career_fair_interactions_1.event
31. 11.558 28.779 ↓ 345.1 4,486 1

Sort (cost=25.51..25.54 rows=13 width=160) (actual time=28.267..28.779 rows=4,486 loops=1)

  • Sort Key: career_fair_interactions_1.student_id, career_fair_interactions_1.event
  • Sort Method: quicksort Memory: 1,254kB
32. 0.948 17.221 ↓ 345.1 4,486 1

Hash Join (cost=0.42..25.27 rows=13 width=160) (actual time=16.274..17.221 rows=4,486 loops=1)

  • Hash Cond: (organization_interaction_sources_1.id = career_fair_interactions_1.organization_interaction_source_id)
33. 0.015 0.015 ↑ 356.7 3 1

Seq Scan on organization_interaction_sources organization_interaction_sources_1 (cost=0.00..20.70 rows=1,070 width=120) (actual time=0.011..0.015 rows=3 loops=1)

34. 1.652 16.258 ↓ 345.1 4,486 1

Hash (cost=0.26..0.26 rows=13 width=72) (actual time=16.258..16.258 rows=4,486 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 558kB
35. 14.606 14.606 ↓ 345.1 4,486 1

CTE Scan on career_fair_interactions career_fair_interactions_1 (cost=0.00..0.26 rows=13 width=72) (actual time=0.001..14.606 rows=4,486 loops=1)

36.          

CTE student_resumes

37. 11.893 22.804 ↓ 1.0 3,327 1

WindowAgg (cost=345.93..410.89 rows=3,248 width=48) (actual time=10.591..22.804 rows=3,327 loops=1)

38. 9.920 10.911 ↓ 1.0 3,327 1

Sort (cost=345.93..354.05 rows=3,248 width=40) (actual time=10.586..10.911 rows=3,327 loops=1)

  • Sort Key: documents.student_id, documents.created_at
  • Sort Method: quicksort Memory: 356kB
39. 0.991 0.991 ↓ 1.0 3,327 1

Seq Scan on documents (cost=0.00..156.48 rows=3,248 width=40) (actual time=0.017..0.991 rows=3,327 loops=1)

40. 35.400 477.938 ↓ 4,332.0 4,332 1

Nested Loop (cost=1.32..14.40 rows=1 width=262) (actual time=147.880..477.938 rows=4,332 loops=1)

41. 29.503 429.542 ↓ 4,332.0 4,332 1

Nested Loop (cost=1.16..11.15 rows=1 width=246) (actual time=147.866..429.542 rows=4,332 loops=1)

42. 24.945 374.047 ↓ 4,332.0 4,332 1

Nested Loop (cost=0.89..8.69 rows=1 width=246) (actual time=147.850..374.047 rows=4,332 loops=1)

43. 7.487 301.450 ↓ 4,332.0 4,332 1

Nested Loop (cost=0.74..8.51 rows=1 width=230) (actual time=147.838..301.450 rows=4,332 loops=1)

44. 9.040 172.841 ↓ 4,486.0 4,486 1

Hash Join (cost=0.46..0.82 rows=1 width=185) (actual time=147.812..172.841 rows=4,486 loops=1)

  • Hash Cond: ((career_fair_interactions.student_id = career_fair_student_journeys.student_id) AND (career_fair_interactions.event = career_fair_student_journeys.event))
45. 100.863 100.863 ↓ 345.1 4,486 1

CTE Scan on career_fair_interactions (cost=0.00..0.26 rows=13 width=137) (actual time=84.859..100.863 rows=4,486 loops=1)

46. 9.766 62.938 ↓ 178.5 2,320 1

Hash (cost=0.26..0.26 rows=13 width=80) (actual time=62.938..62.938 rows=2,320 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 277kB
47. 53.172 53.172 ↓ 178.5 2,320 1

CTE Scan on career_fair_student_journeys (cost=0.00..0.26 rows=13 width=80) (actual time=28.293..53.172 rows=2,320 loops=1)

48. 121.122 121.122 ↑ 1.0 1 4,486

Index Scan using students_pkey on students (cost=0.28..7.69 rows=1 width=77) (actual time=0.027..0.027 rows=1 loops=4,486)

  • Index Cond: (id = career_fair_interactions.student_id)
  • Filter: ((email)::text !~~* '%yello.co%'::text)
  • Rows Removed by Filter: 0
49. 47.652 47.652 ↑ 1.0 1 4,332

Index Scan using student_sources_pkey on student_sources (cost=0.15..0.18 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=4,332)

  • Index Cond: (id = students.student_source_id)
50. 25.992 25.992 ↑ 1.0 1 4,332

Index Scan using organizations_pkey on organizations (cost=0.27..2.44 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=4,332)

  • Index Cond: (id = career_fair_interactions.organization_id)
51. 12.996 12.996 ↑ 1.0 1 4,332

Index Scan using organization_interaction_sources_pkey on organization_interaction_sources (cost=0.15..3.25 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=4,332)

  • Index Cond: (id = career_fair_interactions.organization_interaction_source_id)
52. 8,352.096 8,352.096 ↓ 177.7 2,843 4,332

CTE Scan on student_resumes (cost=0.00..73.08 rows=16 width=40) (actual time=0.003..1.928 rows=2,843 loops=4,332)

  • Filter: (row_count = 1)
  • Rows Removed by Filter: 484