explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dsi9 : Optimization for: plan #w3Mg

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 68,547.376 460,103.739 ↑ 13.7 273,493,269 1

Merge Right Join (cost=65,895,010.48..141,749,465.52 rows=3,744,446,561 width=4) (actual time=280,314.469..460,103.739 rows=273,493,269 loops=1)

  • Merge Cond: (fa.facility_id = pp.orderingfacilityid)
2. 0.012 0.021 ↑ 71.9 27 1

Sort (cost=135.34..140.19 rows=1,940 width=12) (actual time=0.014..0.021 rows=27 loops=1)

  • Sort Key: fa.facility_id
  • Sort Method: quicksort Memory: 26kB
3. 0.009 0.009 ↑ 71.9 27 1

Seq Scan on mat_distinctfacilityaddress fa (cost=0.00..29.40 rows=1,940 width=12) (actual time=0.004..0.009 rows=27 loops=1)

4. 39,078.867 391,556.342 ↑ 1.4 273,493,269 1

Materialize (cost=65,894,875.14..67,825,903.38 rows=386,205,648 width=8) (actual time=280,314.442..391,556.342 rows=273,493,269 loops=1)

5. 218,807.518 352,477.475 ↑ 1.4 273,493,269 1

Sort (cost=65,894,875.14..66,860,389.26 rows=386,205,648 width=8) (actual time=280,314.440..352,477.475 rows=273,493,269 loops=1)

  • Sort Key: pp.orderingfacilityid
  • Sort Method: external merge Disk: 4811520kB
6. 36,514.006 133,669.957 ↑ 1.4 273,493,269 1

Merge Right Join (cost=78,253.87..7,041,152.34 rows=386,205,648 width=8) (actual time=789.343..133,669.957 rows=273,493,269 loops=1)

  • Merge Cond: (pcn.patient_id = p.patient_id)
7. 111.818 111.818 ↓ 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..111.818 rows=306,274 loops=1)

  • Index Cond: (default_contact_number = true)
  • Filter: (default_contact_number IS TRUE)
  • Heap Fetches: 46557
8. 38,120.695 97,044.133 ↑ 1.4 273,493,269 1

Materialize (cost=78,253.45..2,826,960.96 rows=386,205,648 width=16) (actual time=789.326..97,044.133 rows=273,493,269 loops=1)

9. 40,083.142 58,923.438 ↑ 1.4 273,493,269 1

Merge Join (cost=78,253.45..1,861,446.84 rows=386,205,648 width=16) (actual time=789.325..58,923.438 rows=273,493,269 loops=1)

  • Merge Cond: (pp.patientid = p.patient_id)
10. 1,053.375 1,053.375 ↑ 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.007..1,053.375 rows=3,515,832 loops=1)

11. 13,522.386 17,786.921 ↓ 77.8 273,493,260 1

Materialize (cost=78,198.97..756,224.37 rows=3,515,837 width=28) (actual time=789.316..17,786.921 rows=273,493,260 loops=1)

12. 536.279 4,264.535 ↑ 1.0 3,515,831 1

Merge Join (cost=78,198.97..747,434.78 rows=3,515,837 width=28) (actual time=789.315..4,264.535 rows=3,515,831 loops=1)

  • Merge Cond: (p.patient_id = ppd.patientid)
13. 155.597 1,226.869 ↑ 1.0 364,248 1

Merge Join (cost=78,196.48..106,165.44 rows=364,376 width=20) (actual time=789.275..1,226.869 rows=364,248 loops=1)

  • Merge Cond: (p_1.patient_id = p.patient_id)
14. 236.976 236.976 ↑ 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.018..236.976 rows=364,250 loops=1)

  • Heap Fetches: 286112
15. 157.146 834.296 ↑ 1.0 364,248 1

Sort (cost=78,196.06..79,107.00 rows=364,376 width=12) (actual time=789.252..834.296 rows=364,248 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 29364kB
16. 76.298 677.150 ↑ 1.0 364,282 1

Hash Join (cost=20,759.53..44,536.70 rows=364,376 width=12) (actual time=216.509..677.150 rows=364,282 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
17. 329.816 600.849 ↑ 1.0 364,284 1

Hash Right Join (cost=20,758.46..39,525.46 rows=364,376 width=16) (actual time=216.498..600.849 rows=364,284 loops=1)

  • Hash Cond: (pa.patient_id = p.patient_id)
18. 54.645 54.645 ↓ 1.2 364,216 1

Seq Scan on mat_patientaddressdata pa (cost=0.00..12,639.00 rows=306,400 width=20) (actual time=0.005..54.645 rows=364,216 loops=1)

19. 72.845 216.388 ↑ 1.0 364,284 1

Hash (cost=16,203.76..16,203.76 rows=364,376 width=20) (actual time=216.388..216.388 rows=364,284 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 19908kB
20. 143.543 143.543 ↑ 1.0 364,284 1

Seq Scan on mst_pm_patient p (cost=0.00..16,203.76 rows=364,376 width=20) (actual time=0.003..143.543 rows=364,284 loops=1)

21. 0.001 0.003 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.002 0.002 ↑ 1.0 3 1

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

23. 2,501.387 2,501.387 ↑ 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.033..2,501.387 rows=3,515,832 loops=1)

  • Heap Fetches: 3515832
Planning time : 2.462 ms
Execution time : 471,046.638 ms