explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Aoc : Optimization for: plan #pUZa

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 84,776.511 217,348.229 ↑ 1.4 273,493,269 1

Hash Left Join (cost=83,696.10..14,332,787.40 rows=386,205,648 width=4) (actual time=1,876.989..217,348.229 rows=273,493,269 loops=1)

  • Hash Cond: (pp.orderingfacilityid = mst_am_facility_address.facility_id)
2. 35,570.322 132,571.677 ↑ 1.4 273,493,269 1

Merge Right Join (cost=83,693.37..7,093,231.06 rows=386,205,648 width=8) (actual time=1,876.921..132,571.677 rows=273,493,269 loops=1)

  • Merge Cond: (pcn.patient_id = p.patient_id)
3. 100.779 100.779 ↓ 1.0 306,274 1

Index Only Scan using mst_pm_contact_number_patient_id_default_contact_idx on mst_pm_contact_number pcn (cost=0.42..23,544.31 rows=304,212 width=8) (actual time=0.014..100.779 rows=306,274 loops=1)

  • Index Cond: (default_contact_number = true)
  • Filter: (default_contact_number IS TRUE)
  • Heap Fetches: 46557
4. 37,054.959 96,900.576 ↑ 1.4 273,493,269 1

Materialize (cost=83,692.95..2,879,039.69 rows=386,205,648 width=16) (actual time=1,876.903..96,900.576 rows=273,493,269 loops=1)

5. 39,371.309 59,845.617 ↑ 1.4 273,493,269 1

Merge Join (cost=83,692.95..1,913,525.57 rows=386,205,648 width=16) (actual time=1,876.902..59,845.617 rows=273,493,269 loops=1)

  • Merge Cond: (pp.patientid = p.patient_id)
6. 874.081 874.081 ↑ 1.0 3,515,832 1

Index Scan using mat_patientprescriptiondata_patientid_idx on mat_patientprescriptiondata pp (cost=0.43..596,412.34 rows=3,515,837 width=12) (actual time=0.008..874.081 rows=3,515,832 loops=1)

7. 13,840.261 19,600.227 ↓ 77.8 273,493,260 1

Materialize (cost=83,635.13..808,306.23 rows=3,515,837 width=28) (actual time=1,876.892..19,600.227 rows=273,493,260 loops=1)

8. 519.505 5,759.966 ↑ 1.0 3,515,831 1

Merge Join (cost=83,635.13..799,516.63 rows=3,515,837 width=28) (actual time=1,876.889..5,759.966 rows=3,515,831 loops=1)

  • Merge Cond: (p.patient_id = ppd.patientid)
9. 156.170 2,949.982 ↑ 1.0 364,248 1

Merge Left Join (cost=83,629.31..158,250.37 rows=364,376 width=20) (actual time=1,876.824..2,949.982 rows=364,248 loops=1)

  • Merge Cond: (p.patient_id = pa.patient_id)
10. 262.956 866.917 ↑ 1.0 364,248 1

Nested Loop (cost=0.84..71,874.38 rows=364,376 width=20) (actual time=0.029..866.917 rows=364,248 loops=1)

  • Join Filter: (p.gender_gender_id = g.gender_id)
  • Rows Removed by Join Filter: 728501
11. 261.960 603.961 ↑ 1.0 364,250 1

Merge Join (cost=0.84..55,476.43 rows=364,376 width=24) (actual time=0.026..603.961 rows=364,250 loops=1)

  • Merge Cond: (p.patient_id = p_1.patient_id)
12. 125.879 125.879 ↑ 1.0 364,250 1

Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p (cost=0.42..28,417.98 rows=364,376 width=20) (actual time=0.007..125.879 rows=364,250 loops=1)

13. 216.122 216.122 ↑ 1.0 364,250 1

Index Only Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p_1 (cost=0.42..21,592.81 rows=364,376 width=8) (actual time=0.016..216.122 rows=364,250 loops=1)

  • Heap Fetches: 286112
14. 0.000 0.000 ↑ 1.0 3 364,250

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.000 rows=3 loops=364,250)

15. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on ref_co_gender g (cost=0.00..1.03 rows=3 width=4) (actual time=0.002..0.005 rows=3 loops=1)

16. 121.531 1,926.895 ↓ 3.0 364,197 1

Sort (cost=83,628.46..83,934.56 rows=122,439 width=20) (actual time=1,876.791..1,926.895 rows=364,197 loops=1)

  • Sort Key: pa.patient_id
  • Sort Method: quicksort Memory: 39831kB
17. 36.806 1,805.364 ↓ 3.0 364,216 1

Subquery Scan on pa (cost=63,792.32..73,281.32 rows=122,439 width=20) (actual time=599.365..1,805.364 rows=364,216 loops=1)

18. 1,768.558 1,768.558 ↓ 3.0 364,216 1

CTE Scan on totalpatientaddress (cost=63,792.32..72,056.93 rows=122,439 width=3,707) (actual time=599.365..1,768.558 rows=364,216 loops=1)

  • Filter: (totaladdress > 0)
  • Rows Removed by Filter: 3101
19.          

CTE totalpatientaddress

20. 421.369 1,365.801 ↓ 1.0 367,317 1

WindowAgg (cost=48,181.39..63,792.32 rows=367,316 width=204) (actual time=599.359..1,365.801 rows=367,317 loops=1)

21. 269.075 944.432 ↓ 1.0 367,317 1

WindowAgg (cost=48,181.39..56,446.00 rows=367,316 width=204) (actual time=599.340..944.432 rows=367,317 loops=1)

22. 504.771 675.357 ↓ 1.0 367,317 1

Sort (cost=48,181.39..49,099.68 rows=367,316 width=204) (actual time=599.331..675.357 rows=367,317 loops=1)

  • Sort Key: pa_1.patient_id, pa_1.address_type_address_type_id
  • Sort Method: external merge Disk: 60408kB
23. 170.586 170.586 ↓ 1.0 367,317 1

Seq Scan on mst_pm_address pa_1 (cost=0.00..14,229.16 rows=367,316 width=204) (actual time=0.015..170.586 rows=367,317 loops=1)

24. 2,290.479 2,290.479 ↑ 1.0 3,515,832 1

Index Only Scan using mat_patientprescriptiondata_patientid_idx on mat_patientprescriptiondata ppd (cost=0.43..596,412.34 rows=3,515,837 width=8) (actual time=0.041..2,290.479 rows=3,515,832 loops=1)

  • Heap Fetches: 3515832
25. 0.009 0.041 ↓ 1.0 27 1

Hash (cost=2.40..2.40 rows=26 width=12) (actual time=0.041..0.041 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
26. 0.005 0.032 ↓ 1.0 27 1

Unique (cost=1.99..2.14 rows=26 width=12) (actual time=0.027..0.032 rows=27 loops=1)

27. 0.012 0.027 ↑ 1.0 29 1

Sort (cost=1.99..2.07 rows=29 width=12) (actual time=0.026..0.027 rows=29 loops=1)

  • Sort Key: mst_am_facility_address.facility_id
  • Sort Method: quicksort Memory: 26kB
28. 0.015 0.015 ↑ 1.0 29 1

Seq Scan on mst_am_facility_address (cost=0.00..1.29 rows=29 width=12) (actual time=0.005..0.015 rows=29 loops=1)

Planning time : 3.224 ms
Execution time : 226,830.332 ms