explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pUZa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 68,463.997 474,029.082 ↑ 13.7 273,493,269 1

Merge Right Join (cost=65,882,644.93..141,737,099.97 rows=3,744,446,561 width=4) (actual time=284,061.064..474,029.082 rows=273,493,269 loops=1)

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

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

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

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

4. 40,520.987 405,565.059 ↑ 1.4 273,493,269 1

Materialize (cost=65,882,509.59..67,813,537.83 rows=386,205,648 width=8) (actual time=284,061.036..405,565.059 rows=273,493,269 loops=1)

5. 228,865.725 365,044.072 ↑ 1.4 273,493,269 1

Sort (cost=65,882,509.59..66,848,023.71 rows=386,205,648 width=8) (actual time=284,061.034..365,044.072 rows=273,493,269 loops=1)

  • Sort Key: pp.orderingfacilityid
  • Sort Method: external merge Disk: 4811520kB
6. 36,891.553 136,178.347 ↑ 1.4 273,493,269 1

Merge Right Join (cost=78,253.87..7,028,786.79 rows=386,205,648 width=8) (actual time=869.977..136,178.347 rows=273,493,269 loops=1)

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

Index Scan using mst_pm_contact_number_patient_id_idx on mst_pm_contact_number pcn (cost=0.42..49,567.14 rows=300,591 width=8) (actual time=0.006..546.074 rows=306,274 loops=1)

  • Filter: (default_contact_number IS TRUE)
  • Rows Removed by Filter: 630629
8. 39,318.262 98,740.720 ↑ 1.4 273,493,269 1

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

9. 40,104.129 59,422.458 ↑ 1.4 273,493,269 1

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

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

11. 13,498.472 18,220.236 ↓ 77.8 273,493,260 1

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

12. 526.856 4,721.764 ↑ 1.0 3,515,831 1

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

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

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

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

  • Heap Fetches: 286112
15. 156.106 914.977 ↑ 1.0 364,248 1

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

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

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

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

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

  • Hash Cond: (pa.patient_id = p.patient_id)
18. 62.406 62.406 ↓ 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.004..62.406 rows=364,216 loops=1)

19. 71.857 235.984 ↑ 1.0 364,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 19908kB
20. 164.127 164.127 ↑ 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..164.127 rows=364,284 loops=1)

21. 0.003 0.004 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.001 0.001 ↑ 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.001 rows=3 loops=1)

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

  • Heap Fetches: 3515832
Planning time : 3.966 ms
Execution time : 485,010.512 ms