explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DFp5

Settings
# exclusive inclusive rows x rows loops node
1. 22.870 2,366.587 ↑ 3.3 128 1

Subquery Scan on survey_links_view (cost=71,357.04..87,575.62 rows=427 width=216) (actual time=1,672.361..2,366.587 rows=128 loops=1)

  • Filter: (survey_links_view.study_id = 980)
  • Rows Removed by Filter: 412029
2. 380.996 2,343.717 ↓ 4.8 412,157 1

WindowAgg (cost=71,357.04..86,507.09 rows=85,482 width=257) (actual time=1,603.331..2,343.717 rows=412,157 loops=1)

3. 186.477 1,962.721 ↓ 4.8 412,157 1

Group (cost=71,357.04..82,874.11 rows=85,482 width=198) (actual time=1,603.319..1,962.721 rows=412,157 loops=1)

  • Group Key: sbj.id, sbj_status.lookup_code, rse.id, evcrf.id, rc.name, alc.lookup_code, link.id, evdef.repeating, rcv.version_name, srv.id, evdef.name, r.id
4. 65.361 1,776.244 ↓ 5.8 412,157 1

Gather Merge (cost=71,357.04..80,737.03 rows=71,236 width=198) (actual time=1,603.316..1,776.244 rows=412,157 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 102.739 1,710.883 ↓ 3.9 137,386 3 / 3

Group (cost=70,357.02..71,514.60 rows=35,618 width=198) (actual time=1,574.295..1,710.883 rows=137,386 loops=3)

  • Group Key: sbj.id, sbj_status.lookup_code, rse.id, evcrf.id, rc.name, alc.lookup_code, link.id, evdef.repeating, rcv.version_name, srv.id, evdef.name, r.id
6. 209.157 1,608.144 ↓ 3.9 137,386 3 / 3

Sort (cost=70,357.02..70,446.06 rows=35,618 width=198) (actual time=1,574.291..1,608.144 rows=137,386 loops=3)

  • Sort Key: sbj.id DESC, sbj_status.lookup_code, rse.id, evcrf.id, rc.name, alc.lookup_code, link.id, evdef.repeating, rcv.version_name, srv.id, evdef.name, r.id
  • Sort Method: external merge Disk: 30312kB
  • Worker 0: Sort Method: external merge Disk: 30840kB
  • Worker 1: Sort Method: external merge Disk: 28000kB
7. 33.982 1,398.987 ↓ 3.9 137,386 3 / 3

Hash Left Join (cost=43,714.28..64,255.24 rows=35,618 width=198) (actual time=776.944..1,398.987 rows=137,386 loops=3)

  • Hash Cond: (sbj.status_id = sbj_status.id)
8. 35.365 1,362.826 ↓ 3.9 137,386 3 / 3

Parallel Hash Left Join (cost=43,342.59..63,790.01 rows=35,618 width=197) (actual time=774.731..1,362.826 rows=137,386 loops=3)

  • Hash Cond: (sbj.id = r.subject_id)
9. 31.439 1,320.101 ↓ 3.9 137,254 3 / 3

Hash Left Join (cost=41,434.46..61,203.06 rows=35,618 width=189) (actual time=767.228..1,320.101 rows=137,254 loops=3)

  • Hash Cond: (evcrf.status_id = alc.id)
10. 33.424 1,286.052 ↓ 3.9 137,254 3 / 3

Hash Left Join (cost=41,062.77..60,737.82 rows=35,618 width=188) (actual time=764.599..1,286.052 rows=137,254 loops=3)

  • Hash Cond: (rse.study_event_definition_id = evdef.id)
11. 52.680 1,250.746 ↓ 3.9 137,254 3 / 3

Parallel Hash Left Join (cost=40,770.36..60,351.85 rows=35,618 width=178) (actual time=762.701..1,250.746 rows=137,254 loops=3)

  • Hash Cond: (evcrf.id = link.event_crf_id)
12. 129.466 1,174.558 ↓ 3.8 134,965 3 / 3

Nested Loop Left Join (cost=36,027.26..55,457.53 rows=35,618 width=153) (actual time=738.909..1,174.558 rows=134,965 loops=3)

  • Filter: (rse.repeating_form_parent_id IS NULL)
  • Rows Removed by Filter: 2249
13. 70.179 907.878 ↓ 3.8 137,214 3 / 3

Merge Join (cost=36,026.84..37,500.04 rows=36,467 width=133) (actual time=738.865..907.878 rows=137,214 loops=3)

  • Merge Cond: (sbj.id = evcrf.subject_id)
14. 28.679 45.070 ↑ 1.3 72,353 3 / 3

Sort (cost=18,178.72..18,404.84 rows=90,445 width=40) (actual time=38.715..45.070 rows=72,353 loops=3)

  • Sort Key: sbj.id
  • Sort Method: external merge Disk: 4048kB
  • Worker 0: Sort Method: external merge Disk: 3592kB
  • Worker 1: Sort Method: external merge Disk: 3584kB
15. 16.391 16.391 ↑ 1.2 72,356 3 / 3

Parallel Seq Scan on rc_subjects sbj (cost=0.00..8,258.45 rows=90,445 width=40) (actual time=0.021..16.391 rows=72,356 loops=3)

16. 36.579 792.629 ↓ 4.7 411,643 3 / 3

Materialize (cost=17,848.08..18,285.68 rows=87,521 width=101) (actual time=700.119..792.629 rows=411,643 loops=3)

17. 251.052 756.050 ↓ 4.7 411,643 3 / 3

Sort (cost=17,848.08..18,066.88 rows=87,521 width=101) (actual time=700.115..756.050 rows=411,643 loops=3)

  • Sort Key: evcrf.subject_id
  • Sort Method: external merge Disk: 50328kB
  • Worker 0: Sort Method: external merge Disk: 50328kB
  • Worker 1: Sort Method: external merge Disk: 50328kB
18. 113.759 504.998 ↓ 4.7 411,643 3 / 3

Nested Loop (cost=472.27..5,875.77 rows=87,521 width=101) (actual time=2.751..504.998 rows=411,643 loops=3)

  • Join Filter: (srv.crf_version_id = evcrf.crf_version_id)
19. 0.830 8.007 ↓ 7.0 998 3 / 3

Hash Join (cost=471.84..854.83 rows=142 width=85) (actual time=2.706..8.007 rows=998 loops=3)

  • Hash Cond: (rcv.id = srv.crf_version_id)
20. 2.779 6.576 ↑ 1.0 1,447 3 / 3

Hash Join (cost=373.03..749.12 rows=1,459 width=31) (actual time=2.097..6.576 rows=1,447 loops=3)

  • Hash Cond: (rcv.crf_id = rc.id)
21. 1.716 1.716 ↑ 1.0 10,383 3 / 3

Seq Scan on rc_crf_versions rcv (cost=0.00..348.83 rows=10,383 width=17) (actual time=0.006..1.716 rows=10,383 loops=3)

22. 0.162 2.081 ↑ 1.0 1,339 3 / 3

Hash (cost=356.29..356.29 rows=1,339 width=30) (actual time=2.081..2.081 rows=1,339 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 105kB
23. 1.919 1.919 ↑ 1.0 1,339 3 / 3

Seq Scan on rc_crfs rc (cost=0.00..356.29 rows=1,339 width=30) (actual time=0.009..1.919 rows=1,339 loops=3)

  • Filter: enable_survey
  • Rows Removed by Filter: 8190
24. 0.129 0.601 ↑ 1.0 1,014 3 / 3

Hash (cost=86.14..86.14 rows=1,014 width=54) (actual time=0.601..0.601 rows=1,014 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
25. 0.472 0.472 ↑ 1.0 1,014 3 / 3

Seq Scan on rc_surveys srv (cost=0.00..86.14 rows=1,014 width=54) (actual time=0.027..0.472 rows=1,014 loops=3)

26. 383.232 383.232 ↑ 1.5 412 2,994 / 3

Index Scan using rc_event_crfs_crf_version_id_idx on rc_event_crfs evcrf (cost=0.43..27.68 rows=614 width=40) (actual time=0.005..0.384 rows=412 loops=2,994)

  • Index Cond: (crf_version_id = rcv.id)
27. 137.214 137.214 ↑ 1.0 1 411,643 / 3

Index Scan using rc_study_events_pk on rc_study_events rse (cost=0.42..0.48 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=411,643)

  • Index Cond: (evcrf.study_event_id = id)
28. 11.725 23.508 ↑ 1.8 46,932 3 / 3

Parallel Hash (cost=3,704.71..3,704.71 rows=83,071 width=33) (actual time=23.508..23.508 rows=46,932 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 11776kB
29. 11.783 11.783 ↑ 1.8 46,932 3 / 3

Parallel Seq Scan on rc_survey_link link (cost=0.00..3,704.71 rows=83,071 width=33) (actual time=0.020..11.783 rows=46,932 loops=3)

30. 0.607 1.882 ↑ 1.0 5,707 3 / 3

Hash (cost=221.07..221.07 rows=5,707 width=26) (actual time=1.881..1.882 rows=5,707 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 413kB
31. 1.275 1.275 ↑ 1.0 5,707 3 / 3

Seq Scan on rc_study_event_definitions evdef (cost=0.00..221.07 rows=5,707 width=26) (actual time=0.007..1.275 rows=5,707 loops=3)

32. 1.068 2.610 ↑ 1.0 9,764 3 / 3

Hash (cost=249.64..249.64 rows=9,764 width=17) (actual time=2.610..2.610 rows=9,764 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 635kB
33. 1.542 1.542 ↑ 1.0 9,764 3 / 3

Seq Scan on ad_lookup_codes alc (cost=0.00..249.64 rows=9,764 width=17) (actual time=0.011..1.542 rows=9,764 loops=3)

34. 3.544 7.360 ↑ 1.8 19,169 3 / 3

Parallel Hash (cost=1,485.28..1,485.28 rows=33,828 width=16) (actual time=7.360..7.360 rows=19,169 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 3264kB
35. 3.816 3.816 ↑ 1.8 19,169 3 / 3

Parallel Seq Scan on rc_subject_entry r (cost=0.00..1,485.28 rows=33,828 width=16) (actual time=0.015..3.816 rows=19,169 loops=3)

36. 1.055 2.179 ↑ 1.0 9,764 3 / 3

Hash (cost=249.64..249.64 rows=9,764 width=17) (actual time=2.179..2.179 rows=9,764 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 635kB
37. 1.124 1.124 ↑ 1.0 9,764 3 / 3

Seq Scan on ad_lookup_codes sbj_status (cost=0.00..249.64 rows=9,764 width=17) (actual time=0.003..1.124 rows=9,764 loops=3)

Planning time : 12.356 ms
Execution time : 2,386.823 ms