explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fGUo

Settings
# exclusive inclusive rows x rows loops node
1. 10,883.341 20,453.631 ↑ 1.0 1,007,453 1

Hash Left Join (cost=230,386.96..1,899,807.16 rows=1,008,561 width=3,899) (actual time=4,400.202..20,453.631 rows=1,007,453 loops=1)

  • Hash Cond: (pips.patient_policy_id = ppds.patient_policy_id)
2. 397.028 9,441.781 ↑ 1.0 1,007,453 1

Hash Left Join (cost=213,292.34..372,439.60 rows=1,008,561 width=3,885) (actual time=4,271.565..9,441.781 rows=1,007,453 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pips.patient_id)::text)
3. 486.583 9,044.738 ↑ 1.0 1,007,453 1

Hash Left Join (cost=213,289.30..368,654.25 rows=1,008,561 width=3,895) (actual time=4,271.546..9,044.738 rows=1,007,453 loops=1)

  • Hash Cond: ((pd.patient_city)::text = (ci.city_id)::text)
4. 383.508 8,558.110 ↑ 1.0 1,007,453 1

Nested Loop (cost=213,283.68..354,780.91 rows=1,008,561 width=3,883) (actual time=4,271.495..8,558.110 rows=1,007,453 loops=1)

  • Join Filter: (pd.patient_group = cgm.confidentiality_grp_id)
5. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on confidentiality_grp_master cgm (cost=0.00..1.01 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

6. 477.414 8,174.598 ↑ 1.0 1,007,453 1

Hash Left Join (cost=213,283.68..342,172.89 rows=1,008,561 width=3,875) (actual time=4,271.487..8,174.598 rows=1,007,453 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
7. 1,506.346 7,697.172 ↑ 1.0 1,007,453 1

Merge Left Join (cost=213,282.32..328,303.82 rows=1,008,561 width=3,871) (actual time=4,271.460..7,697.172 rows=1,007,453 loops=1)

  • Merge Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
  • Join Filter: (((pd.previous_visit_id)::text = (pr.patient_id)::text) AND ((pr.patient_id)::text = (CASE WHEN ((pd.visit_id)::text <> ''::text) THEN pd.visit_id ELSE pd.previous_visit_id END)::text))
  • Rows Removed by Join Filter: 253,253
8. 496.810 496.810 ↑ 1.0 1,007,453 1

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..95,126.05 rows=1,008,561 width=3,732) (actual time=0.008..496.810 rows=1,007,453 loops=1)

9. 159.011 5,694.016 ↓ 1.0 698,621 1

Materialize (cost=213,281.89..216,756.79 rows=694,979 width=139) (actual time=4,271.446..5,694.016 rows=698,621 loops=1)

10. 3,965.370 5,535.005 ↓ 1.0 698,621 1

Sort (cost=213,281.89..215,019.34 rows=694,979 width=139) (actual time=4,271.443..5,535.005 rows=698,621 loops=1)

  • Sort Key: pr.mr_no
  • Sort Method: external merge Disk: 57,104kB
11. 717.410 1,569.635 ↓ 1.0 698,621 1

Hash Left Join (cost=42,781.16..116,627.31 rows=694,979 width=139) (actual time=514.110..1,569.635 rows=698,621 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pipp.patient_id)::text)
12. 338.219 338.219 ↓ 1.0 698,621 1

Seq Scan on patient_registration pr (cost=0.00..34,226.79 rows=694,979 width=121) (actual time=0.004..338.219 rows=698,621 loops=1)

13. 85.514 514.006 ↓ 1.0 411,364 1

Hash (cost=34,882.45..34,882.45 rows=408,457 width=31) (actual time=514.006..514.006 rows=411,364 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 6,456kB
14. 136.335 428.492 ↓ 1.0 411,364 1

Merge Left Join (cost=0.84..34,882.45 rows=408,457 width=31) (actual time=0.021..428.492 rows=411,364 loops=1)

  • Merge Cond: (pipp.patient_policy_id = ppdp.patient_policy_id)
15. 180.654 180.654 ↓ 1.0 411,364 1

Index Scan using idx_patient_insurance_plans_patient_policy_id on patient_insurance_plans pipp (cost=0.42..15,919.05 rows=408,457 width=17) (actual time=0.011..180.654 rows=411,364 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 4
16. 111.503 111.503 ↓ 1.0 411,369 1

Index Scan using idx_ppd_patient_policy_id on patient_policy_details ppdp (cost=0.42..12,897.05 rows=408,472 width=22) (actual time=0.008..111.503 rows=411,369 loops=1)

17. 0.004 0.012 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=13) (actual time=0.012..0.012 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
18. 0.008 0.008 ↑ 1.0 16 1

Seq Scan on salutation_master sal (cost=0.00..1.16 rows=16 width=13) (actual time=0.005..0.008 rows=16 loops=1)

19. 0.020 0.045 ↑ 1.0 161 1

Hash (cost=3.61..3.61 rows=161 width=19) (actual time=0.045..0.045 rows=161 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.025 0.025 ↑ 1.0 161 1

Seq Scan on city ci (cost=0.00..3.61 rows=161 width=19) (actual time=0.003..0.025 rows=161 loops=1)

21. 0.002 0.015 ↑ 3.5 4 1

Hash (cost=2.87..2.87 rows=14 width=17) (actual time=0.015..0.015 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
22. 0.013 0.013 ↑ 3.5 4 1

Index Scan using patient_insurance_plans_priority_idx on patient_insurance_plans pips (cost=0.42..2.87 rows=14 width=17) (actual time=0.010..0.013 rows=4 loops=1)

  • Index Cond: (priority = 2)
23. 63.534 128.509 ↓ 1.0 411,369 1

Hash (cost=9,594.72..9,594.72 rows=408,472 width=22) (actual time=128.509..128.509 rows=411,369 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 5,584kB
24. 64.975 64.975 ↓ 1.0 411,369 1

Seq Scan on patient_policy_details ppds (cost=0.00..9,594.72 rows=408,472 width=22) (actual time=0.003..64.975 rows=411,369 loops=1)

Total runtime : 20,523.182 ms