explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TfzA : getSubjectsForSurvey

Settings
# exclusive inclusive rows x rows loops node
1. 1.047 955.512 ↓ 1,266.0 1,266 1

Sort (cost=2,664,954.56..2,664,954.57 rows=1 width=117) (actual time=955.380..955.512 rows=1,266 loops=1)

  • Sort Key: se.date_start
  • Sort Method: quicksort Memory: 231kB
2.          

CTE email_field

3. 2.614 2.614 ↑ 1.0 30 1

Seq Scan on rc_study_settings sett (cost=0.00..406.19 rows=30 width=16) (actual time=0.953..2.614 rows=30 loops=1)

  • Filter: ((key)::text = 'Survey Email CRF Field'::text)
  • Rows Removed by Filter: 18367
4. 1.722 954.465 ↓ 1,266.0 1,266 1

Nested Loop Semi Join (cost=13,275.61..2,664,548.36 rows=1 width=117) (actual time=361.788..954.465 rows=1,266 loops=1)

  • Join Filter: ((srv.crf_id = rcv.crf_id) AND (edc.default_version_id = rec.crf_version_id))
  • Rows Removed by Join Filter: 1943
5. 0.930 877.920 ↓ 1,527.0 1,527 1

Nested Loop (cost=13,274.61..2,664,527.55 rows=1 width=150) (actual time=360.424..877.920 rows=1,527 loops=1)

  • Join Filter: ((srv.crf_id = edc.crf_id) AND (srv.crf_version_id = edc.default_version_id))
6. 0.345 863.004 ↓ 1,554.0 1,554 1

Nested Loop (cost=13,274.19..2,664,520.74 rows=1 width=142) (actual time=360.076..863.004 rows=1,554 loops=1)

7. 0.879 856.443 ↓ 1,554.0 1,554 1

Nested Loop Anti Join (cost=13,273.91..2,664,514.90 rows=1 width=134) (actual time=359.950..856.443 rows=1,554 loops=1)

  • Join Filter: (rsl.survey_id = srv.id)
  • Rows Removed by Join Filter: 4
8. 80.311 470.776 ↓ 1,577.0 1,577 1

Nested Loop (cost=13,258.91..2,664,482.99 rows=1 width=134) (actual time=359.820..470.776 rows=1,577 loops=1)

  • Join Filter: ((se.tenant_id = srv.tenant_id) AND (sched.survey_id = srv.id))
  • Rows Removed by Join Filter: 838383
9. 3.881 3.881 ↓ 1.1 506 1

Index Scan using rc_surveys_crf_id_idx on rc_surveys srv (cost=0.28..2,415.78 rows=440 width=32) (actual time=0.380..3.881 rows=506 loops=1)

  • Filter: ((survey_enabled = 1) AND (calendared_invitation_enabled = 1) AND (survey_expiration > now()))
  • Rows Removed by Filter: 587
10. 31.795 386.584 ↓ 48.8 1,660 506

Materialize (cost=13,258.63..2,661,805.49 rows=34 width=118) (actual time=0.565..0.764 rows=1,660 loops=506)

11. 0.576 354.789 ↓ 48.8 1,660 1

Nested Loop (cost=13,258.63..2,661,805.32 rows=34 width=118) (actual time=285.925..354.789 rows=1,660 loops=1)

  • Join Filter: (sbj.status_id <> alc.id)
  • Rows Removed by Join Filter: 24
12. 0.005 0.569 ↑ 1.0 1 1

Nested Loop (cost=4.58..19.97 rows=1 width=8) (actual time=0.567..0.569 rows=1 loops=1)

  • Join Filter: (alt.id = alc.lookup_type_id)
13. 0.281 0.281 ↑ 1.0 1 1

Index Scan using ad_lookup_types_u4 on ad_lookup_types alt (cost=0.28..8.30 rows=1 width=8) (actual time=0.280..0.281 rows=1 loops=1)

  • Index Cond: ((lookup_type)::text = 'Subject Status'::text)
14. 0.093 0.283 ↑ 2.0 1 1

Bitmap Heap Scan on ad_lookup_codes alc (cost=4.30..11.65 rows=2 width=16) (actual time=0.283..0.283 rows=1 loops=1)

  • Recheck Cond: ((lookup_code)::text = 'Removed'::text)
  • Heap Blocks: exact=1
15. 0.190 0.190 ↓ 1.5 3 1

Bitmap Index Scan on ad_lookup_codes_u2 (cost=0.00..4.30 rows=2 width=0) (actual time=0.190..0.190 rows=3 loops=1)

  • Index Cond: ((lookup_code)::text = 'Removed'::text)
16. 0.769 353.644 ↑ 33.8 1,684 1

Nested Loop Left Join (cost=13,254.05..2,661,074.09 rows=56,901 width=126) (actual time=285.355..353.644 rows=1,684 loops=1)

17. 1.025 332.667 ↑ 33.8 1,684 1

Nested Loop Left Join (cost=13,253.61..2,313,523.99 rows=56,901 width=127) (actual time=284.972..332.667 rows=1,684 loops=1)

18. 0.000 286.174 ↑ 33.8 1,684 1

Hash Left Join (cost=13,253.05..44,596.62 rows=56,901 width=127) (actual time=284.222..286.174 rows=1,684 loops=1)

  • Hash Cond: (sbj.study_id = email_field.study_id)
19. 5.107 285.564 ↑ 33.8 1,684 1

Gather (cost=13,252.07..44,350.00 rows=56,901 width=127) (actual time=281.575..285.564 rows=1,684 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 7.738 280.457 ↑ 42.3 561 3 / 3

Parallel Hash Join (cost=12,252.07..37,659.90 rows=23,709 width=127) (actual time=275.499..280.457 rows=561 loops=3)

  • Hash Cond: (se.subject_id = sbj.id)
21. 28.707 185.497 ↑ 42.3 561 3 / 3

Hash Join (cost=1,277.28..25,118.86 rows=23,709 width=85) (actual time=21.577..185.497 rows=561 loops=3)

  • Hash Cond: (sed.event_definition_type_id = lc.id)
  • Join Filter: ((((lc.lookup_code)::text = 'Calendared'::text) AND (CURRENT_TIMESTAMP <= ((se.date_start + '2 days'::interval) + ('1 day'::interval * (CASE sed.day_max WHEN 0 THEN 1 ELSE LEAST(sed.day_max, 10) END)::double precision)))) OR (((lc.lookup_code)::text <> 'Calendared'::text) AND (CURRENT_TIMESTAMP <= (se.date_start + '2 days'::interval))))
  • Rows Removed by Join Filter: 55736
22. 25.595 152.736 ↑ 1.3 56,298 3 / 3

Hash Join (cost=896.66..24,551.44 rows=71,130 width=84) (actual time=10.339..152.736 rows=56,298 loops=3)

  • Hash Cond: (se.study_event_definition_id = sed.id)
23. 116.961 116.961 ↓ 2.1 235,568 3 / 3

Parallel Seq Scan on rc_study_events se (cost=0.00..22,518.12 rows=113,431 width=40) (actual time=0.137..116.961 rows=235,568 loops=3)

  • Filter: (CURRENT_TIMESTAMP >= (date_start - '1 day'::interval))
  • Rows Removed by Filter: 32732
24. 0.628 10.180 ↑ 1.0 3,761 3 / 3

Hash (cost=849.47..849.47 rows=3,775 width=44) (actual time=10.180..10.180 rows=3,761 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 324kB
25. 0.832 9.552 ↑ 1.0 3,761 3 / 3

Hash Join (cost=308.45..849.47 rows=3,775 width=44) (actual time=3.825..9.552 rows=3,761 loops=3)

  • Hash Cond: (sched.study_event_definition_id = sed.id)
26. 5.197 5.197 ↑ 1.0 3,775 3 / 3

Seq Scan on rc_survey_scheduler sched (cost=0.00..531.10 rows=3,775 width=16) (actual time=0.280..5.197 rows=3,775 loops=3)

  • Filter: (active = 1)
  • Rows Removed by Filter: 73
27. 0.825 3.523 ↑ 1.0 6,020 3 / 3

Hash (cost=233.20..233.20 rows=6,020 width=28) (actual time=3.523..3.523 rows=6,020 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 418kB
28. 2.698 2.698 ↑ 1.0 6,020 3 / 3

Seq Scan on rc_study_event_definitions sed (cost=0.00..233.20 rows=6,020 width=28) (actual time=0.011..2.698 rows=6,020 loops=3)

29. 1.189 4.054 ↑ 1.0 9,983 3 / 3

Hash (cost=255.83..255.83 rows=9,983 width=17) (actual time=4.054..4.054 rows=9,983 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 646kB
30. 2.865 2.865 ↑ 1.0 9,983 3 / 3

Seq Scan on ad_lookup_codes lc (cost=0.00..255.83 rows=9,983 width=17) (actual time=0.021..2.865 rows=9,983 loops=3)

31. 13.934 87.222 ↑ 1.2 77,682 3 / 3

Parallel Hash (cost=8,907.02..8,907.02 rows=97,102 width=42) (actual time=87.222..87.222 rows=77,682 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2304kB
32. 73.288 73.288 ↑ 1.2 77,682 3 / 3

Parallel Seq Scan on rc_subjects sbj (cost=0.00..8,907.02 rows=97,102 width=42) (actual time=0.090..73.288 rows=77,682 loops=3)

33. 0.008 2.637 ↑ 1.0 30 1

Hash (cost=0.60..0.60 rows=30 width=16) (actual time=2.637..2.637 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 2.629 2.629 ↑ 1.0 30 1

CTE Scan on email_field (cost=0.00..0.60 rows=30 width=16) (actual time=0.956..2.629 rows=30 loops=1)

35. 45.468 45.468 ↓ 0.0 0 1,684

Index Scan using rc_item_data_idx1 on rc_item_data rid (cost=0.56..39.86 rows=1 width=24) (actual time=0.026..0.027 rows=0 loops=1,684)

  • Index Cond: ((sbj.id = subject_id) AND (rc_item_form_metadata_id = email_field.ifm_id))
36. 20.208 20.208 ↓ 0.0 0 1,684

Index Scan using i_rc_item_data_values_rc_item_data_id on rc_item_data_values ridv (cost=0.44..6.09 rows=2 width=15) (actual time=0.012..0.012 rows=0 loops=1,684)

  • Index Cond: (rc_item_data_id = rid.id)
37. 22.668 384.788 ↓ 0.0 0 1,577

Hash Join (cost=15.00..31.90 rows=1 width=24) (actual time=0.242..0.244 rows=0 loops=1,577)

  • Hash Cond: (rsl.event_crf_id = u.id)
38. 237.380 237.380 ↓ 10.4 187 1,430

Index Scan using rc_survey_link_idx1 on rc_survey_link rsl (cost=0.42..17.27 rows=18 width=24) (actual time=0.016..0.166 rows=187 loops=1,430)

  • Index Cond: (subject_id = se.subject_id)
  • Filter: (recent = 1)
  • Rows Removed by Filter: 2
39. 2.970 124.740 ↑ 2.7 3 1,485

Hash (cost=14.48..14.48 rows=8 width=16) (actual time=0.084..0.084 rows=3 loops=1,485)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 121.770 121.770 ↑ 2.7 3 1,485

Index Scan using rc_event_crfs_n7 on rc_event_crfs u (cost=0.43..14.48 rows=8 width=16) (actual time=0.062..0.082 rows=3 loops=1,485)

  • Index Cond: (study_event_id = se.id)
41. 6.216 6.216 ↑ 1.0 1 1,554

Index Scan using rc_crfs_pk on rc_crfs crf (cost=0.29..5.83 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,554)

  • Index Cond: (id = srv.crf_id)
  • Filter: enable_survey
42. 13.986 13.986 ↑ 1.0 1 1,554

Index Scan using rc_event_definition_crfs_u1 on rc_event_definition_crfs edc (cost=0.42..6.80 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1,554)

  • Index Cond: ((study_event_definition_id = se.study_event_definition_id) AND (crf_id = crf.id) AND (parent_id IS NULL))
43. 1.071 74.823 ↓ 2.0 2 1,527

Nested Loop (cost=1.00..20.79 rows=1 width=40) (actual time=0.041..0.049 rows=2 loops=1,527)

44. 0.843 67.188 ↓ 2.0 2 1,527

Nested Loop (cost=0.71..11.58 rows=1 width=48) (actual time=0.038..0.044 rows=2 loops=1,527)

45. 56.499 56.499 ↓ 2.0 2 1,527

Index Scan using rc_event_crfs_u1 on rc_event_crfs rec (cost=0.43..5.94 rows=1 width=32) (actual time=0.035..0.037 rows=2 loops=1,527)

  • Index Cond: ((study_event_id = se.id) AND (subject_id = se.subject_id))
46. 9.846 9.846 ↑ 1.0 1 3,282

Index Scan using rc_crf_versions_pk on rc_crf_versions rcv (cost=0.29..5.64 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,282)

  • Index Cond: (id = rec.crf_version_id)
47. 6.564 6.564 ↑ 1.0 1 3,282

Index Scan using ad_lookup_codes_pk on ad_lookup_codes lc_1 (cost=0.29..5.64 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,282)

  • Index Cond: (id = rec.status_id)
  • Filter: ((lookup_code)::text = 'Not Started'::text)
  • Rows Removed by Filter: 0
Planning time : 76.684 ms
Execution time : 959.119 ms