explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPsa

Settings
# exclusive inclusive rows x rows loops node
1. 10,926.304 25,617.096 ↑ 1.0 1,007,452 1

Nested Loop Left Join (cost=164,270.81..1,810,304.96 rows=1,008,561 width=3,907) (actual time=5,370.673..25,617.096 rows=1,007,452 loops=1)

2. 408.586 13,683.340 ↑ 1.0 1,007,452 1

Hash Left Join (cost=164,270.39..813,957.62 rows=1,008,561 width=3,893) (actual time=5,369.295..13,683.340 rows=1,007,452 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pips.patient_id)::text)
3. 527.105 13,274.666 ↑ 1.0 1,007,452 1

Hash Left Join (cost=164,230.25..810,135.18 rows=1,008,561 width=3,889) (actual time=5,369.203..13,274.666 rows=1,007,452 loops=1)

  • Hash Cond: ((pd.patient_city)::text = (ci.city_id)::text)
4. 364.857 12,747.500 ↑ 1.0 1,007,452 1

Nested Loop (cost=164,224.63..796,261.84 rows=1,008,561 width=3,877) (actual time=5,369.136..12,747.500 rows=1,007,452 loops=1)

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

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

6. 508.022 12,382.618 ↑ 1.0 1,007,452 1

Hash Left Join (cost=164,224.63..783,653.82 rows=1,008,561 width=3,869) (actual time=5,369.107..12,382.618 rows=1,007,452 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
7. 610.535 11,874.586 ↑ 1.0 1,007,452 1

Nested Loop Left Join (cost=164,223.27..769,784.74 rows=1,008,561 width=3,865) (actual time=5,369.062..11,874.586 rows=1,007,452 loops=1)

8. 1,531.915 8,241.695 ↑ 1.0 1,007,452 1

Merge Left Join (cost=164,222.84..277,506.89 rows=1,008,561 width=3,845) (actual time=5,369.055..8,241.695 rows=1,007,452 loops=1)

  • Merge Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
  • Join Filter: ((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: 252,045
9. 552.722 552.722 ↑ 1.0 1,007,452 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.007..552.722 rows=1,007,452 loops=1)

10. 152.276 6,157.058 ↓ 1.0 698,620 1

Materialize (cost=164,222.42..167,697.31 rows=694,979 width=125) (actual time=5,369.041..6,157.058 rows=698,620 loops=1)

11. 4,302.099 6,004.782 ↓ 1.0 698,620 1

Sort (cost=164,222.42..165,959.86 rows=694,979 width=125) (actual time=5,369.038..6,004.782 rows=698,620 loops=1)

  • Sort Key: pr.mr_no
  • Sort Method: external merge Disk: 52,296kB
12. 851.859 1,702.683 ↓ 1.0 698,620 1

Merge Left Join (cost=0.98..70,349.68 rows=694,979 width=125) (actual time=0.015..1,702.683 rows=698,620 loops=1)

  • Merge Cond: ((pr.patient_id)::text = (pipp.patient_id)::text)
13. 510.537 510.537 ↓ 1.0 698,620 1

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..46,197.70 rows=694,979 width=125) (actual time=0.004..510.537 rows=698,620 loops=1)

14. 340.287 340.287 ↓ 1.0 411,363 1

Index Scan using policy_patient_idx on patient_insurance_plans pipp (cost=0.42..17,311.16 rows=408,457 width=21) (actual time=0.006..340.287 rows=411,363 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 4
15. 3,022.356 3,022.356 ↓ 0.0 0 1,007,452

Index Scan using patient_registration_pkey on patient_registration prvs_pr (cost=0.42..0.48 rows=1 width=34) (actual time=0.003..0.003 rows=0 loops=1,007,452)

  • Index Cond: ((pd.previous_visit_id)::text = (patient_id)::text)
16. 0.005 0.010 ↑ 1.0 16 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
17. 0.005 0.005 ↑ 1.0 16 1

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

18. 0.024 0.061 ↑ 1.0 161 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.037 0.037 ↑ 1.0 161 1

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

20. 0.000 0.088 ↑ 3.5 4 1

Hash (cost=39.97..39.97 rows=14 width=31) (actual time=0.088..0.088 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
21. 0.005 0.088 ↑ 3.5 4 1

Nested Loop Left Join (cost=0.84..39.97 rows=14 width=31) (actual time=0.043..0.088 rows=4 loops=1)

22. 0.015 0.015 ↑ 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=21) (actual time=0.013..0.015 rows=4 loops=1)

  • Index Cond: (priority = 2)
23. 0.068 0.068 ↑ 1.0 1 4

Index Scan using idx_ppd_patient_policy_id on patient_policy_details ppds (cost=0.42..2.64 rows=1 width=22) (actual time=0.016..0.017 rows=1 loops=4)

  • Index Cond: (patient_policy_id = pips.patient_policy_id)
24. 1,007.452 1,007.452 ↓ 0.0 0 1,007,452

Index Scan using idx_ppd_patient_policy_id on patient_policy_details ppdp (cost=0.42..0.46 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=1,007,452)

  • Index Cond: (patient_policy_id = pipp.patient_policy_id)
Total runtime : 25,688.834 ms