explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rqSkZ : 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 44,174.249 ↑ 5.5 2 1

Limit (cost=9,058,836.04..9,068,161.12 rows=11 width=4) (actual time=44,174.177..44,174.249 rows=2 loops=1)

2. 0.005 44,174.247 ↑ 34.5 2 1

Nested Loop Left Join (cost=9,058,836.04..9,117,329.71 rows=69 width=4) (actual time=44,174.176..44,174.247 rows=2 loops=1)

  • Join Filter: (addr.party_id = i.id)
  • Rows Removed by Join Filter: 2
3. 9.371 44,174.108 ↑ 34.5 2 1

Nested Loop Left Join (cost=9,058,835.61..9,117,327.59 rows=69 width=8) (actual time=44,174.038..44,174.108 rows=2 loops=1)

  • Filter: (mc.closing_step_id IS NULL)
  • Rows Removed by Filter: 7840
4. 2.368 44,102.001 ↓ 2.3 7,842 1

Nested Loop Left Join (cost=9,058,835.04..9,088,313.09 rows=3,400 width=12) (actual time=43,977.618..44,102.001 rows=7,842 loops=1)

5. 7.779 43,997.687 ↓ 2.3 7,842 1

Merge Left Join (cost=9,058,834.61..9,060,329.09 rows=3,400 width=16) (actual time=43,977.552..43,997.687 rows=7,842 loops=1)

  • Merge Cond: (mep.id = mea.event_patient_id)
  • Filter: ((mea.denial IS NULL) OR (mea.denial IS FALSE))
6. 1.747 43,975.230 ↓ 2.3 7,842 1

Merge Left Join (cost=9,058,834.32..9,058,853.62 rows=3,400 width=16) (actual time=43,971.299..43,975.230 rows=7,842 loops=1)

  • Merge Cond: (mep.id = mdoa.event_patient_id)
7. 12.239 43,973.477 ↓ 2.3 7,842 1

Sort (cost=9,058,800.60..9,058,809.10 rows=3,400 width=16) (actual time=43,971.290..43,973.477 rows=7,842 loops=1)

  • Sort Key: mep.id
  • Sort Method: quicksort Memory: 560kB
8. 4.293 43,961.238 ↓ 2.3 7,842 1

Nested Loop (cost=0.42..9,058,601.17 rows=3,400 width=16) (actual time=0.256..43,961.238 rows=7,842 loops=1)

9. 0.106 0.106 ↑ 1.0 1 1

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

  • Filter: (id = 1436)
  • Rows Removed by Filter: 329
10. 51.725 43,956.839 ↓ 2.3 7,842 1

Index Scan using idx_md_event_patient on md_event_patient mep (cost=0.42..9,058,556.05 rows=3,400 width=20) (actual time=0.151..43,956.839 rows=7,842 loops=1)

  • Index Cond: (event_id = 1436)
  • Filter: ((is_deleted IS NOT TRUE) AND (case_id_2stage IS NULL) AND ((SubPlan 2) IS NOT TRUE))
  • Rows Removed by Filter: 286
11.          

SubPlan (forIndex Scan)

12. 16,462.457 43,905.114 ↓ 0.0 0 7,843

Merge Join (cost=85.07..1,115.59 rows=1 width=0) (actual time=5.598..5.598 rows=0 loops=7,843)

  • Merge Cond: (ref.service_id = msr.id)
13. 27,128.937 27,128.937 ↑ 51.1 32,911 7,843

Index Only Scan using md_referral_to_service_fk on md_referral ref (cost=0.43..43,890.82 rows=1,680,426 width=4) (actual time=0.009..3.459 rows=32,911 loops=7,843)

  • Heap Fetches: 3652879
14. 70.587 313.720 ↑ 104.7 6 7,843

Sort (cost=84.52..86.09 rows=628 width=4) (actual time=0.039..0.040 rows=6 loops=7,843)

  • Sort Key: msr.id
  • Sort Method: quicksort Memory: 25kB
15. 243.133 243.133 ↑ 104.7 6 7,843

Index Scan using md_srv_rendered_case_id_idx on md_srv_rendered msr (cost=0.57..55.33 rows=628 width=4) (actual time=0.021..0.031 rows=6 loops=7,843)

  • Index Cond: (case_id = mep.case_id)
16. 0.005 0.006 ↓ 0.0 0 1

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

  • Sort Key: mdoa.event_patient_id
  • Sort Method: quicksort Memory: 25kB
17. 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)

18. 14.678 14.678 ↓ 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.014..14.678 rows=32,868 loops=1)

19. 101.946 101.946 ↑ 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.013..0.013 rows=1 loops=7,842)

  • Index Cond: (id = mep.indiv_id)
20. 62.736 62.736 ↑ 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.008..0.008 rows=1 loops=7,842)

  • Index Cond: (id = mep.case_id)
21. 0.009 0.134 ↑ 1.0 1 2

Materialize (cost=0.43..1.09 rows=1 width=4) (actual time=0.067..0.067 rows=1 loops=2)

22. 0.000 0.125 ↑ 1.0 1 1

Subquery Scan on addr (cost=0.43..1.09 rows=1 width=4) (actual time=0.125..0.125 rows=1 loops=1)

23. 0.002 0.125 ↑ 1.0 1 1

Limit (cost=0.43..1.08 rows=1 width=4) (actual time=0.125..0.125 rows=1 loops=1)

24. 0.003 0.123 ↑ 7,102,805.0 1 1

Nested Loop (cost=0.43..4,580,625.01 rows=7,102,805 width=4) (actual time=0.123..0.123 rows=1 loops=1)

25. 0.045 0.091 ↑ 7,102,805.0 1 1

Nested Loop (cost=0.00..1,266,200.71 rows=7,102,805 width=4) (actual time=0.091..0.091 rows=1 loops=1)

  • Join Filter: (ppatat.address_type_id = pat.id)
  • Rows Removed by Join Filter: 256
26. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on pim_address_type pat (cost=0.00..1.07 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1)

  • Filter: ((code)::text = 'ACTUAL'::text)
  • Rows Removed by Filter: 2
27. 0.027 0.027 ↑ 165,824.2 257 1

Seq Scan on pim_party_addr_to_addr_type ppatat (cost=0.00..733,489.28 rows=42,616,828 width=8) (actual time=0.006..0.027 rows=257 loops=1)

28. 0.029 0.029 ↑ 1.0 1 1

Index Scan using pim_party_address_pk on pim_party_address ppa (cost=0.43..0.46 rows=1 width=12) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (id = ppatat.party_address_id)
Planning time : 15.971 ms
Execution time : 44,174.599 ms