explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g3dg : getSubjects_prod

Settings
# exclusive inclusive rows x rows loops node
1. 0.546 75,889.759 ↓ 442.0 442 1

Sort (cost=3,076,853.97..3,076,853.98 rows=1 width=117) (actual time=75,889.720..75,889.759 rows=442 loops=1)

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

CTE email_field

3. 1.238 1.286 ↑ 2.0 35 1

Bitmap Heap Scan on rc_study_settings sett (cost=4.84..147.91 rows=71 width=16) (actual time=0.131..1.286 rows=35 loops=1)

  • Recheck Cond: ((key)::text = 'Survey Email CRF Field'::text)
  • Heap Blocks: exact=31
4. 0.048 0.048 ↑ 2.0 35 1

Bitmap Index Scan on rc_study_settings_key_idx (cost=0.00..4.82 rows=71 width=0) (actual time=0.048..0.048 rows=35 loops=1)

  • Index Cond: ((key)::text = 'Survey Email CRF Field'::text)
5. 0.761 75,889.213 ↓ 442.0 442 1

Nested Loop Semi Join (cost=59,775.46..3,076,706.06 rows=1 width=117) (actual time=73,343.158..75,889.213 rows=442 loops=1)

  • Join Filter: ((srv.crf_id = rcv.crf_id) AND (edc.default_version_id = rec.crf_version_id))
  • Rows Removed by Join Filter: 1502
6. 0.850 75,612.163 ↓ 689.0 689 1

Nested Loop (cost=59,774.47..3,076,680.02 rows=1 width=137) (actual time=73,342.459..75,612.163 rows=689 loops=1)

  • Join Filter: (sbj.status_id <> alc.id)
  • Rows Removed by Join Filter: 82
7. 5.346 75,597.435 ↓ 771.0 771 1

Merge Join (cost=59,769.89..3,076,659.84 rows=1 width=145) (actual time=73,341.642..75,597.435 rows=771 loops=1)

  • Merge Cond: (srv.crf_id = edc.crf_id)
  • Join Filter: ((se.study_event_definition_id = edc.study_event_definition_id) AND (srv.crf_version_id = edc.default_version_id))
  • Rows Removed by Join Filter: 4766
8. 2.333 75,551.221 ↓ 23.7 828 1

Nested Loop Anti Join (cost=51,845.58..3,157,329.77 rows=35 width=137) (actual time=73,304.018..75,551.221 rows=828 loops=1)

  • Join Filter: (rsl.survey_id = srv.id)
  • Rows Removed by Join Filter: 1458
9. 687.940 74,153.543 ↓ 56.6 2,037 1

Nested Loop (cost=51,827.30..3,156,439.80 rows=36 width=137) (actual time=73,303.525..74,153.543 rows=2,037 loops=1)

  • Join Filter: (srv.crf_id = crf.id)
  • Rows Removed by Join Filter: 3031056
10. 16.211 16.211 ↓ 1.0 1,489 1

Index Scan using rc_crfs_pk on rc_crfs crf (cost=0.29..11,437.22 rows=1,461 width=8) (actual time=0.212..16.211 rows=1,489 loops=1)

  • Filter: enable_survey
  • Rows Removed by Filter: 8644
11. 253.978 73,449.392 ↓ 8.1 2,037 1,489

Materialize (cost=51,827.02..3,139,458.71 rows=253 width=129) (actual time=1.029..49.328 rows=2,037 loops=1,489)

12. 1.278 73,195.414 ↓ 8.1 2,037 1

Hash Join (cost=51,827.02..3,139,457.45 rows=253 width=129) (actual time=1,532.300..73,195.414 rows=2,037 loops=1)

  • Hash Cond: ((se.tenant_id = srv.tenant_id) AND (sched.survey_id = srv.id))
13. 7.529 73,178.135 ↑ 26.3 2,155 1

Nested Loop Left Join (cost=51,711.15..3,139,044.56 rows=56,574 width=113) (actual time=1,516.272..73,178.135 rows=2,155 loops=1)

14. 18,109.014 72,998.206 ↑ 26.3 2,155 1

Hash Right Join (cost=51,710.72..2,712,219.57 rows=56,574 width=114) (actual time=1,516.230..72,998.206 rows=2,155 loops=1)

  • Hash Cond: ((rid.subject_id = sbj.id) AND (rid.rc_item_form_metadata_id = email_field.ifm_id))
15. 53,781.440 53,781.440 ↓ 1.0 51,117,586 1

Seq Scan on rc_item_data rid (cost=0.00..1,678,245.36 rows=51,057,536 width=24) (actual time=0.566..53,781.440 rows=51,117,586 loops=1)

16. 1.524 1,107.752 ↑ 26.3 2,155 1

Hash (cost=49,867.11..49,867.11 rows=56,574 width=114) (actual time=1,107.752..1,107.752 rows=2,155 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 672kB
17. 0.570 1,106.228 ↑ 26.3 2,155 1

Hash Left Join (cost=16,246.54..49,867.11 rows=56,574 width=114) (actual time=549.976..1,106.228 rows=2,155 loops=1)

  • Hash Cond: (sbj.study_id = email_field.study_id)
18. 17.873 1,104.342 ↑ 26.3 2,155 1

Gather (cost=16,244.23..49,575.99 rows=56,574 width=114) (actual time=548.638..1,104.342 rows=2,155 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 27.615 1,086.469 ↑ 32.8 718 3 / 3

Hash Join (cost=15,244.23..42,918.59 rows=23,572 width=114) (actual time=572.929..1,086.469 rows=718 loops=3)

  • Hash Cond: (se.subject_id = sbj.id)
20. 104.928 560.894 ↑ 32.8 718 3 / 3

Hash Join (cost=1,222.81..26,531.28 rows=23,572 width=85) (actual time=70.823..560.894 rows=718 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: 55577
21. 91.672 436.539 ↑ 1.3 56,296 3 / 3

Hash Join (cost=684.04..25,806.78 rows=70,722 width=84) (actual time=23.908..436.539 rows=56,296 loops=3)

  • Hash Cond: (se.study_event_definition_id = sed.id)
22. 321.030 321.030 ↓ 2.1 244,713 3 / 3

Parallel Seq Scan on rc_study_events se (cost=0.00..23,987.62 rows=114,107 width=40) (actual time=0.019..321.030 rows=244,713 loops=3)

  • Filter: (CURRENT_TIMESTAMP >= (date_start - '1 day'::interval))
  • Rows Removed by Filter: 32939
23. 1.166 23.837 ↑ 1.0 3,791 3 / 3

Hash (cost=636.50..636.50 rows=3,803 width=44) (actual time=23.837..23.837 rows=3,791 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 327kB
24. 1.404 22.671 ↑ 1.0 3,791 3 / 3

Hash Join (cost=321.06..636.50 rows=3,803 width=44) (actual time=15.127..22.671 rows=3,791 loops=3)

  • Hash Cond: (sched.study_event_definition_id = sed.id)
25. 6.208 6.208 ↓ 1.0 3,805 3 / 3

Seq Scan on rc_survey_scheduler sched (cost=0.00..305.45 rows=3,803 width=16) (actual time=0.014..6.208 rows=3,805 loops=3)

  • Filter: (active = 1)
  • Rows Removed by Filter: 73
26. 1.588 15.059 ↑ 1.0 6,106 3 / 3

Hash (cost=244.36..244.36 rows=6,136 width=28) (actual time=15.059..15.059 rows=6,106 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 423kB
27. 13.471 13.471 ↑ 1.0 6,106 3 / 3

Seq Scan on rc_study_event_definitions sed (cost=0.00..244.36 rows=6,136 width=28) (actual time=0.013..13.471 rows=6,106 loops=3)

28. 6.365 19.427 ↑ 1.0 10,034 3 / 3

Hash (cost=413.34..413.34 rows=10,034 width=17) (actual time=19.427..19.427 rows=10,034 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 649kB
29. 13.062 13.062 ↑ 1.0 10,034 3 / 3

Seq Scan on ad_lookup_codes lc (cost=0.00..413.34 rows=10,034 width=17) (actual time=0.025..13.062 rows=10,034 loops=3)

30. 196.331 497.960 ↓ 1.0 242,763 3 / 3

Hash (cost=9,333.19..9,333.19 rows=242,419 width=29) (actual time=497.960..497.960 rows=242,763 loops=3)

  • Buckets: 131072 Batches: 4 Memory Usage: 4537kB
31. 301.629 301.629 ↓ 1.0 242,763 3 / 3

Seq Scan on rc_subjects sbj (cost=0.00..9,333.19 rows=242,419 width=29) (actual time=0.021..301.629 rows=242,763 loops=3)

32. 0.011 1.316 ↑ 2.0 35 1

Hash (cost=1.42..1.42 rows=71 width=16) (actual time=1.316..1.316 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
33. 1.305 1.305 ↑ 2.0 35 1

CTE Scan on email_field (cost=0.00..1.42 rows=71 width=16) (actual time=0.135..1.305 rows=35 loops=1)

34. 172.400 172.400 ↓ 0.0 0 2,155

Index Scan using i_rc_item_data_values_rc_item_data_id on rc_item_data_values ridv (cost=0.44..7.52 rows=2 width=15) (actual time=0.079..0.080 rows=0 loops=2,155)

  • Index Cond: (rc_item_data_id = rid.id)
35. 0.173 16.001 ↓ 1.1 510 1

Hash (cost=108.98..108.98 rows=459 width=32) (actual time=16.001..16.001 rows=510 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
36. 15.828 15.828 ↓ 1.1 510 1

Seq Scan on rc_surveys srv (cost=0.00..108.98 rows=459 width=32) (actual time=0.019..15.828 rows=510 loops=1)

  • Filter: ((survey_enabled = 1) AND (calendared_invitation_enabled = 1) AND (survey_expiration > now()))
  • Rows Removed by Filter: 613
37. 17.133 1,395.345 ↑ 1.0 1 2,037

Hash Join (cost=18.28..42.48 rows=1 width=24) (actual time=0.616..0.685 rows=1 loops=2,037)

  • Hash Cond: (rsl.event_crf_id = u.id)
38. 1,060.292 1,060.292 ↓ 2.7 49 1,907

Index Scan using rc_survey_link_idx1 on rc_survey_link rsl (cost=0.42..24.57 rows=18 width=24) (actual time=0.089..0.556 rows=49 loops=1,907)

  • Index Cond: (subject_id = se.subject_id)
  • Filter: (recent = 1)
  • Rows Removed by Filter: 2
39. 3.974 317.920 ↑ 2.7 3 1,987

Hash (cost=17.76..17.76 rows=8 width=16) (actual time=0.160..0.160 rows=3 loops=1,987)

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

Index Scan using rc_event_crfs_n7 on rc_event_crfs u (cost=0.43..17.76 rows=8 width=16) (actual time=0.140..0.158 rows=3 loops=1,987)

  • Index Cond: (study_event_id = se.id)
41. 21.956 40.868 ↓ 1.2 30,790 1

Sort (cost=7,923.47..7,989.31 rows=26,335 width=32) (actual time=37.248..40.868 rows=30,790 loops=1)

  • Sort Key: edc.crf_id
  • Sort Method: quicksort Memory: 2774kB
42. 16.349 18.912 ↑ 1.0 25,668 1

Bitmap Heap Scan on rc_event_definition_crfs edc (cost=640.52..5,989.87 rows=26,335 width=32) (actual time=3.108..18.912 rows=25,668 loops=1)

  • Recheck Cond: (parent_id IS NULL)
  • Heap Blocks: exact=3274
43. 2.563 2.563 ↓ 1.1 27,869 1

Bitmap Index Scan on rc_event_definition_crfs_n11 (cost=0.00..633.93 rows=26,335 width=0) (actual time=2.562..2.563 rows=27,869 loops=1)

  • Index Cond: (parent_id IS NULL)
44. 2.313 13.878 ↑ 1.0 1 771

Nested Loop (cost=4.58..20.17 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=771)

  • Join Filter: (alt.id = alc.lookup_type_id)
45. 5.397 5.397 ↑ 1.0 1 771

Index Scan using ad_lookup_types_u1 on ad_lookup_types alt (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=771)

  • Index Cond: ((lookup_type)::text = 'Subject Status'::text)
46. 1.542 6.168 ↑ 2.0 1 771

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

  • Recheck Cond: ((lookup_code)::text = 'Removed'::text)
  • Heap Blocks: exact=771
47. 4.626 4.626 ↓ 1.5 3 771

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

  • Index Cond: ((lookup_code)::text = 'Removed'::text)
48. 2.126 276.289 ↓ 3.0 3 689

Nested Loop (cost=1.00..26.01 rows=1 width=40) (actual time=0.386..0.401 rows=3 loops=689)

49. 2.185 269.399 ↓ 3.0 3 689

Nested Loop (cost=0.71..14.39 rows=1 width=48) (actual time=0.373..0.391 rows=3 loops=689)

50. 257.686 257.686 ↓ 3.0 3 689

Index Scan using rc_event_crfs_u1 on rc_event_crfs rec (cost=0.43..7.36 rows=1 width=32) (actual time=0.366..0.374 rows=3 loops=689)

  • Index Cond: ((study_event_id = se.id) AND (subject_id = se.subject_id))
51. 9.528 9.528 ↑ 1.0 1 2,382

Index Scan using rc_crf_versions_pk on rc_crf_versions rcv (cost=0.29..7.02 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=2,382)

  • Index Cond: (id = rec.crf_version_id)
52. 4.764 4.764 ↑ 1.0 1 2,382

Index Scan using ad_lookup_codes_pk on ad_lookup_codes lc_1 (cost=0.29..7.09 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,382)

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