explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9c1g

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 44,215.981 ↑ 5.5 2 1

Limit (cost=2,875,341.64..2,884,666.45 rows=11 width=4) (actual time=44,215.953..44,215.981 rows=2 loops=1)

  • Buffers: shared hit=149209 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
2. 5.145 44,215.977 ↑ 34.5 2 1

Nested Loop Left Join (cost=2,875,341.64..2,933,833.62 rows=69 width=4) (actual time=44,215.951..44,215.977 rows=2 loops=1)

  • Filter: (mc.closing_step_id IS NULL)
  • Rows Removed by Filter: 7840
  • Buffers: shared hit=149209 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
3. 8.116 44,179.464 ↓ 2.3 7,842 1

Nested Loop Left Join (cost=2,875,341.08..2,904,819.12 rows=3,400 width=8) (actual time=44,113.321..44,179.464 rows=7,842 loops=1)

  • Buffers: shared hit=109946 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
4. 7.978 44,132.138 ↓ 2.3 7,842 1

Merge Left Join (cost=2,875,340.65..2,876,835.12 rows=3,400 width=16) (actual time=44,113.299..44,132.138 rows=7,842 loops=1)

  • Merge Cond: (mep.id = mea.event_patient_id)
  • Filter: ((mea.denial IS NULL) OR (mea.denial IS FALSE))
  • Buffers: shared hit=78514 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
5. 1.620 44,111.920 ↓ 2.3 7,842 1

Merge Left Join (cost=2,875,340.36..2,875,359.66 rows=3,400 width=16) (actual time=44,108.026..44,111.920 rows=7,842 loops=1)

  • Merge Cond: (mep.id = mdoa.event_patient_id)
  • Buffers: shared hit=67936 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
6. 4.957 44,110.283 ↓ 2.3 7,842 1

Sort (cost=2,875,306.64..2,875,315.14 rows=3,400 width=16) (actual time=44,108.004..44,110.283 rows=7,842 loops=1)

  • Sort Key: mep.id
  • Sort Method: quicksort Memory: 560kB
  • Buffers: shared hit=67936 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
7. 2.136 44,105.326 ↓ 2.3 7,842 1

Nested Loop (cost=0.42..2,875,107.20 rows=3,400 width=16) (actual time=44,097.386..44,105.326 rows=7,842 loops=1)

  • Buffers: shared hit=67936 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
8. 0.057 0.057 ↑ 1.0 1 1

Seq Scan on md_event me (cost=0.00..11.12 rows=1 width=12) (actual time=0.051..0.057 rows=1 loops=1)

  • Filter: (id = 1436)
  • Rows Removed by Filter: 329
  • Buffers: shared hit=7
9. 64.737 44,103.133 ↓ 2.3 7,842 1

Index Scan using idx_md_event_patient on md_event_patient mep (cost=0.42..2,875,062.08 rows=3,400 width=20) (actual time=44,097.330..44,103.133 rows=7,842 loops=1)

  • Index Cond: (event_id = 1436)
  • Filter: ((is_deleted IS NOT TRUE) AND (case_id_2stage IS NULL) AND ((alternatives: SubPlan 2 or hashed SubPlan 3) IS NOT TRUE))
  • Rows Removed by Filter: 286
  • Buffers: shared hit=67929 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
10.          

SubPlan (forIndex Scan)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.99..353.89 rows=1 width=0) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using md_srv_rendered_case_id_idx on md_srv_rendered msr (cost=0.57..55.33 rows=628 width=4) (never executed)

  • Index Cond: (case_id = mep.case_id)
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using md_referral_to_service_fk on md_referral ref (cost=0.43..6.35 rows=124 width=4) (never executed)

  • Index Cond: (service_id = msr.id)
  • Heap Fetches: 0
14. 47.319 44,038.396 ↓ 50.6 14,681 1

Nested Loop (cost=48,092.45..50,587.33 rows=290 width=4) (actual time=504.893..44,038.396 rows=14,681 loops=1)

  • Buffers: shared hit=67409 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
15. 274.256 517.675 ↓ 50.6 14,682 1

HashAggregate (cost=48,091.88..48,094.78 rows=290 width=4) (actual time=497.822..517.675 rows=14,682 loops=1)

  • Group Key: ref_1.service_id
  • Buffers: shared hit=6304
16. 243.419 243.419 ↓ 1.0 1,681,400 1

Index Only Scan using md_referral_to_service_fk on md_referral ref_1 (cost=0.43..43,890.82 rows=1,680,426 width=4) (actual time=0.032..243.419 rows=1,681,400 loops=1)

  • Heap Fetches: 31181
  • Buffers: shared hit=6304
17. 43,473.402 43,473.402 ↑ 1.0 1 14,682

Index Scan using md_srv_rendered_pk on md_srv_rendered msr_1 (cost=0.57..8.59 rows=1 width=8) (actual time=2.961..2.961 rows=1 loops=14,682)

  • Index Cond: (id = ref_1.service_id)
  • Buffers: shared hit=61105 read=12385 dirtied=8634
  • I/O Timings: read=43072.248
18. 0.016 0.017 ↓ 0.0 0 1

Sort (cost=33.72..34.82 rows=440 width=8) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: mdoa.event_patient_id
  • Sort Method: quicksort Memory: 25kB
19. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on md_disp_orphans_absence mdoa (cost=0.00..14.40 rows=440 width=8) (actual time=0.001..0.001 rows=0 loops=1)

20. 12.240 12.240 ↓ 1.0 32,868 1

Index Scan using md_event_agreement_ep_key on md_event_agreement mea (cost=0.29..1,377.87 rows=32,858 width=5) (actual time=0.023..12.240 rows=32,868 loops=1)

  • Buffers: shared hit=10578
21. 39.210 39.210 ↑ 1.0 1 7,842

Index Scan using pim_individual_pk on pim_individual i (cost=0.43..8.22 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=7,842)

  • Index Cond: (id = mep.indiv_id)
  • Buffers: shared hit=31432
22. 31.368 31.368 ↑ 1.0 1 7,842

Index Scan using mc_case_pk on mc_case mc (cost=0.56..8.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=7,842)

  • Index Cond: (id = mep.case_id)
  • Buffers: shared hit=39263
Planning time : 3.226 ms
Execution time : 44,216.192 ms