explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wwfl : getSubjects

Settings
# exclusive inclusive rows x rows loops node
1. 0.441 413.535 ↓ 728.0 728 1

Sort (cost=2,664,956.80..2,664,956.81 rows=1 width=117) (actual time=413.464..413.535 rows=728 loops=1)

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

CTE email_field

3. 1.479 1.479 ↑ 1.0 30 1

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

  • Filter: ((key)::text = 'Survey Email CRF Field'::text)
  • Rows Removed by Filter: 18367
4. 0.158 413.094 ↓ 728.0 728 1

Nested Loop Semi Join (cost=13,275.61..2,664,550.61 rows=1 width=117) (actual time=213.199..413.094 rows=728 loops=1)

  • Join Filter: ((srv.crf_id = rcv.crf_id) AND (edc.default_version_id = rec.crf_version_id))
  • Rows Removed by Join Filter: 949
5. 0.685 404.035 ↓ 989.0 989 1

Nested Loop (cost=13,274.61..2,664,529.79 rows=1 width=150) (actual time=213.143..404.035 rows=989 loops=1)

  • Join Filter: ((srv.crf_id = edc.crf_id) AND (srv.crf_version_id = edc.default_version_id))
6. 0.749 401.318 ↓ 1,016.0 1,016 1

Nested Loop (cost=13,274.19..2,664,522.98 rows=1 width=142) (actual time=213.128..401.318 rows=1,016 loops=1)

7. 0.843 399.553 ↓ 1,016.0 1,016 1

Nested Loop Anti Join (cost=13,273.91..2,664,517.14 rows=1 width=134) (actual time=213.110..399.553 rows=1,016 loops=1)

  • Join Filter: (rsl.survey_id = srv.id)
  • Rows Removed by Join Filter: 436
8. 69.797 291.474 ↓ 1,577.0 1,577 1

Nested Loop (cost=13,258.91..2,664,485.23 rows=1 width=134) (actual time=194.678..291.474 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. 1.061 1.061 ↓ 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.021..1.061 rows=506 loops=1)

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

Materialize (cost=13,258.63..2,661,807.73 rows=34 width=118) (actual time=0.364..0.436 rows=1,660 loops=506)

11. 0.249 193.209 ↓ 48.8 1,660 1

Nested Loop (cost=13,258.63..2,661,807.56 rows=34 width=118) (actual time=184.366..193.209 rows=1,660 loops=1)

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

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

  • Join Filter: (alt.id = alc.lookup_type_id)
13. 0.015 0.015 ↑ 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.014..0.015 rows=1 loops=1)

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

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

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

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

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

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

17. 0.248 191.909 ↑ 33.8 1,684 1

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

18. 0.000 188.293 ↑ 33.8 1,684 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
20. 7.251 184.489 ↑ 42.3 561 3 / 3

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

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

Hash Join (cost=1,277.28..25,118.86 rows=23,709 width=85) (actual time=7.642..141.224 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.123 110.919 ↑ 1.3 56,298 3 / 3

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

  • Hash Cond: (se.study_event_definition_id = sed.id)
23. 80.883 80.883 ↓ 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.022..80.883 rows=235,568 loops=3)

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

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

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

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

  • Hash Cond: (sched.study_event_definition_id = sed.id)
26. 1.368 1.368 ↑ 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.009..1.368 rows=3,775 loops=3)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 418kB
28. 1.440 1.440 ↑ 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.009..1.440 rows=6,020 loops=3)

29. 1.117 2.559 ↑ 1.0 9,983 3 / 3

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

  • Buckets: 16384 Batches: 1 Memory Usage: 646kB
30. 1.442 1.442 ↑ 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.018..1.442 rows=9,983 loops=3)

31. 12.368 36.014 ↑ 1.2 77,682 3 / 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2336kB
32. 23.646 23.646 ↑ 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.012..23.646 rows=77,682 loops=3)

33. 0.005 1.495 ↑ 1.0 30 1

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

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

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

35. 3.368 3.368 ↓ 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.002..0.002 rows=0 loops=1,684)

  • Index Cond: ((sbj.id = subject_id) AND (rc_item_form_metadata_id = email_field.ifm_id))
36. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1,684)

  • Index Cond: (rc_item_data_id = rid.id)
37. 14.744 107.236 ↑ 1.0 1 1,577

Hash Join (cost=15.00..31.90 rows=1 width=24) (actual time=0.066..0.068 rows=1 loops=1,577)

  • Hash Cond: (rsl.event_crf_id = u.id)
38. 86.520 86.520 ↓ 7.3 139 1,442

Index Scan using rc_survey_link_idx1 on rc_survey_link rsl (cost=0.42..17.27 rows=19 width=24) (actual time=0.003..0.060 rows=139 loops=1,442)

  • Index Cond: (subject_id = se.subject_id)
  • Filter: (recent = 1)
  • Rows Removed by Filter: 2
39. 0.000 5.972 ↑ 2.7 3 1,493

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

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

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

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

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

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

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.002..0.002 rows=1 loops=1,016)

  • Index Cond: ((study_event_definition_id = se.study_event_definition_id) AND (crf_id = crf.id) AND (parent_id IS NULL))
43. 1.218 8.901 ↓ 2.0 2 989

Nested Loop (cost=1.00..20.79 rows=1 width=40) (actual time=0.006..0.009 rows=2 loops=989)

44. 1.218 5.934 ↓ 2.0 2 989

Nested Loop (cost=0.71..11.58 rows=1 width=48) (actual time=0.004..0.006 rows=2 loops=989)

45. 2.967 2.967 ↓ 2.0 2 989

Index Scan using rc_event_crfs_u1 on rc_event_crfs rec (cost=0.43..5.94 rows=1 width=32) (actual time=0.002..0.003 rows=2 loops=989)

  • Index Cond: ((study_event_id = se.id) AND (subject_id = se.subject_id))
46. 1.749 1.749 ↑ 1.0 1 1,749

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

  • Index Cond: (id = rec.crf_version_id)
47. 1.749 1.749 ↑ 1.0 1 1,749

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.001..0.001 rows=1 loops=1,749)

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