explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mgp3

Settings
# exclusive inclusive rows x rows loops node
1. 17.414 707.338 ↑ 7.0 8,564 1

Merge Left Join (cost=9.87..15,596,686.59 rows=59,899 width=105) (actual time=9.634..707.338 rows=8,564 loops=1)

  • Merge Cond: (vs.visit_id = next_pa.visit_id)
  • Join Filter: (next_pa.archive_number = (SubPlan 10))
  • Rows Removed by Join Filter: 891
  • Buffers: shared hit=127828 read=6658
  • I/O Timings: read=229.516
2. 292.192 655.554 ↑ 7.0 8,564 1

Merge Join (cost=9.60..13,584,784.65 rows=59,899 width=82) (actual time=0.102..655.554 rows=8,564 loops=1)

  • Merge Cond: (v.visit_id = ve.visit_id)
  • Join Filter: ((ve.event_id >= vs.admit_event_id) AND ((ve.event_type_id = ANY ('{7,16}'::numeric[])) OR (alternatives: SubPlan 5 or hashed SubPlan 6)) AND ((NOT (SubPlan 7)) OR (ve.event_id < (SubPlan 8))))
  • Rows Removed by Join Filter: 669304
  • Buffers: shared hit=112337 read=6327
  • I/O Timings: read=212.236
3. 6.548 51.856 ↑ 1.0 13,850 1

Nested Loop Left Join (cost=9.17..233,172.23 rows=13,850 width=61) (actual time=0.051..51.856 rows=13,850 loops=1)

  • Buffers: shared hit=33317 read=18
  • I/O Timings: read=0.348
4. 8.760 17.608 ↑ 1.0 13,850 1

Merge Join (cost=0.57..2,673.61 rows=13,850 width=53) (actual time=0.021..17.608 rows=13,850 loops=1)

  • Merge Cond: (vs.visit_id = v.visit_id)
  • Buffers: shared hit=5203 read=18
  • I/O Timings: read=0.348
5. 4.522 4.522 ↑ 1.0 13,850 1

Index Scan using kp_visit_segment on visit_segment vs (cost=0.29..1,242.84 rows=13,850 width=30) (actual time=0.010..4.522 rows=13,850 loops=1)

  • Buffers: shared hit=3065 read=1
  • I/O Timings: read=0.023
6. 4.326 4.326 ↑ 1.0 13,764 1

Index Scan using kp_visit on visit v (cost=0.29..1,223.23 rows=13,764 width=23) (actual time=0.005..4.326 rows=13,764 loops=1)

  • Buffers: shared hit=2138 read=17
  • I/O Timings: read=0.325
7. 0.000 27.700 ↓ 0.0 0 13,850

Limit (cost=8.60..16.62 rows=1 width=4,304) (actual time=0.002..0.002 rows=0 loops=13,850)

  • Buffers: shared hit=28114
8.          

Initplan (for Limit)

9. 0.000 27.700 ↑ 1.0 1 13,850

Result (cost=8.31..8.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=13,850)

  • Buffers: shared hit=27843
10.          

Initplan (for Result)

11. 13.850 27.700 ↓ 0.0 0 13,850

Limit (cost=0.29..8.31 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=13,850)

  • Buffers: shared hit=27843
12. 13.850 13.850 ↓ 0.0 0 13,850

Index Only Scan using kp_visit_segment on visit_segment mingtvs (cost=0.29..8.31 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=13,850)

  • Index Cond: ((visit_id = $18) AND (visit_segment_number IS NOT NULL) AND (visit_segment_number > $19))
  • Heap Fetches: 90
  • Buffers: shared hit=27843
13. 0.000 0.000 ↓ 0.0 0 13,850

Index Scan using kp_visit_segment on visit_segment vsinner (cost=0.29..8.30 rows=1 width=4,304) (actual time=0.000..0.000 rows=0 loops=13,850)

  • Index Cond: ((visit_id = vs.visit_id) AND (visit_segment_number = $21))
  • Buffers: shared hit=271
14. 149.407 149.407 ↓ 1.3 677,876 1

Index Scan using kp_event on event ve (cost=0.42..38,679.67 rows=527,356 width=26) (actual time=0.008..149.407 rows=677,876 loops=1)

  • Buffers: shared hit=54355 read=3204
  • I/O Timings: read=42.815
15.          

SubPlan (for Merge Join)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using kp_proc_event on proc_event pe (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((visit_id = vs.visit_id) AND (event_id = ve.event_id))
  • Filter: (proc_id = ANY ('{470,2821}'::numeric[]))
17. 52.971 152.109 ↑ 136.0 1 1

Gather (cost=1,000.00..12,087.30 rows=136 width=13) (actual time=49.808..152.109 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=5428 read=3105
  • I/O Timings: read=169.074
18. 99.138 99.138 ↓ 0.0 0 3

Parallel Seq Scan on proc_event pe_1 (cost=0.00..11,073.70 rows=57 width=13) (actual time=65.126..99.138 rows=0 loops=3)

  • Filter: (proc_id = ANY ('{470,2821}'::numeric[]))
  • Rows Removed by Filter: 162605
  • Buffers: shared hit=5428 read=3105
  • I/O Timings: read=169.074
19. 8.820 8.820 ↓ 0.0 0 8,820

Index Only Scan using kp_visit_segment on visit_segment next_vs (cost=0.29..8.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=8,820)

  • Index Cond: ((visit_id = ve.visit_id) AND (visit_segment_number > vs.visit_segment_number))
  • Heap Fetches: 390
  • Buffers: shared hit=18063
20. 0.390 1.170 ↑ 1.0 1 390

Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=390)

  • Buffers: shared hit=1174
21. 0.780 0.780 ↓ 2.0 2 390

Index Scan using kp_visit_segment on visit_segment next_vs_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=2 loops=390)

  • Index Cond: ((visit_id = ve.visit_id) AND (visit_segment_number > vs.visit_segment_number))
  • Buffers: shared hit=1174
22. 0.098 0.301 ↓ 2.9 892 1

Materialize (cost=0.27..37.70 rows=312 width=32) (actual time=0.124..0.301 rows=892 loops=1)

  • Buffers: shared hit=54
23. 0.203 0.203 ↑ 1.0 312 1

Index Scan using kp_physician_archive on physician_archive next_pa (cost=0.27..36.92 rows=312 width=32) (actual time=0.113..0.203 rows=312 loops=1)

  • Buffers: shared hit=54
24.          

SubPlan (for Merge Left Join)

25. 0.000 2.412 ↑ 1.0 1 1,206

Result (cost=8.29..8.30 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,206)

  • Buffers: shared hit=3288
26.          

Initplan (for Result)

27. 0.000 2.412 ↑ 1.0 1 1,206

Limit (cost=0.27..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,206)

  • Buffers: shared hit=3288
28. 2.412 2.412 ↑ 1.0 1 1,206

Index Only Scan using kp_physician_archive on physician_archive pa (cost=0.27..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,206)

  • Index Cond: ((visit_id = ve.visit_id) AND (archive_number IS NOT NULL) AND (archive_number > ve.event_id))
  • Heap Fetches: 874
  • Buffers: shared hit=3288
29. 0.280 0.280 ↑ 1.0 1 140

Index Scan using kp_visit on visit v_1 (cost=0.29..8.30 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=140)

  • Index Cond: (visit_id = vs.visit_id)
  • Buffers: shared hit=420
30. 0.282 0.282 ↑ 1.0 1 141

Index Scan using kp_visit on visit v_2 (cost=0.29..8.30 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=141)

  • Index Cond: (visit_id = vs.visit_id)
  • Buffers: shared hit=423
31. 0.000 8.271 ↑ 1.0 1 919

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=919)

  • Buffers: shared hit=2778 read=86
  • I/O Timings: read=4.650
32. 8.271 8.271 ↑ 1.0 1 919

Index Scan using kp_admit_event_archive on admit_event_archive admitcancel (cost=0.29..8.31 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=919)

  • Index Cond: ((visit_id = vs.visit_id) AND (event_id = vs.admit_event_id))
  • Filter: (archive_type_id = '5'::numeric)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=2778 read=86
  • I/O Timings: read=4.650
33. 2.853 22.824 ↑ 1.0 1 2,853

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=2,853)

  • Buffers: shared hit=8528 read=245
  • I/O Timings: read=12.630
34. 19.971 19.971 ↑ 1.0 1 2,853

Index Scan using kp_admit_event_archive on admit_event_archive admitnoshow (cost=0.29..8.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=2,853)

  • Index Cond: ((visit_id = vs.visit_id) AND (event_id = vs.admit_event_id))
  • Filter: (archive_type_id = '15'::numeric)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=8528 read=245
  • I/O Timings: read=12.630
Planning time : 4.972 ms
Execution time : 711.724 ms