explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nzu2

Settings
# exclusive inclusive rows x rows loops node
1. 0.872 48,215.271 ↓ 0.0 0 1

Hash Join (cost=153,708.92..971,377.57 rows=1 width=109) (actual time=48,215.271..48,215.271 rows=0 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
  • Total runtime: 48215.657 ms
2. 22.966 48,214.319 ↓ 1.4 1,123 1

Hash Left Join (cost=153,692.01..971,357.65 rows=800 width=106) (actual time=6,001.839..48,214.319 rows=1,123 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (setoffs.mr_no)::text)
3. 20.574 42,241.644 ↓ 1.4 1,123 1

HashAggregate (cost=443.96..818,095.96 rows=800 width=10) (actual time=52.108..42,241.644 rows=1,123 loops=1)

4. 8.623 8.623 ↑ 1.0 1,295 1

Seq Scan on patient_deposits pd (cost=0.00..440.73 rows=1,295 width=10) (actual time=0.713..8.623 rows=1,295 loops=1)

  • Filter: ((deposit_available_for)::text = 'I'::text)
  • Rows Removed by Filter: 12779
5.          

SubPlan (forHashAggregate)

6. 4.492 24,255.677 ↑ 1.0 1 1,123

Aggregate (cost=511.02..511.03 rows=1 width=5) (actual time=21.599..21.599 rows=1 loops=1,123)

7. 24,251.185 24,251.185 ↑ 1.0 1 1,123

Seq Scan on patient_deposits pas (cost=0.00..511.01 rows=1 width=5) (actual time=13.853..21.595 rows=1 loops=1,123)

  • Filter: ((realized = 'Y'::bpchar) AND ((deposit_available_for)::text = 'I'::text) AND ((mr_no)::text = (pd.mr_no)::text))
  • Rows Removed by Filter: 14073
8. 3.369 17,956.770 ↑ 1.0 1 1,123

Aggregate (cost=511.02..511.03 rows=1 width=5) (actual time=15.990..15.990 rows=1 loops=1,123)

9. 17,953.401 17,953.401 ↓ 0.0 0 1,123

Seq Scan on patient_deposits pds (cost=0.00..511.01 rows=1 width=5) (actual time=15.987..15.987 rows=0 loops=1,123)

  • Filter: ((realized <> 'Y'::bpchar) AND ((deposit_available_for)::text = 'I'::text) AND ((mr_no)::text = (pd.mr_no)::text))
  • Rows Removed by Filter: 14074
10. 0.485 5,949.709 ↓ 14.2 938 1

Hash (cost=153,247.22..153,247.22 rows=66 width=41) (actual time=5,949.709..5,949.709 rows=938 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
11. 0.442 5,949.224 ↓ 14.2 938 1

Subquery Scan on setoffs (cost=153,245.90..153,247.22 rows=66 width=41) (actual time=5,948.311..5,949.224 rows=938 loops=1)

12. 17.400 5,948.782 ↓ 14.2 938 1

HashAggregate (cost=153,245.90..153,246.56 rows=66 width=12) (actual time=5,948.309..5,948.782 rows=938 loops=1)

13. 1.779 5,931.382 ↓ 14.3 945 1

Nested Loop (cost=0.42..153,245.57 rows=66 width=12) (actual time=1,278.048..5,931.382 rows=945 loops=1)

14. 5,889.913 5,889.913 ↓ 1.0 945 1

Seq Scan on bill b_1 (cost=0.00..145,835.41 rows=914 width=14) (actual time=1,277.997..5,889.913 rows=945 loops=1)

  • Filter: (ip_deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 1538961
15. 39.690 39.690 ↑ 1.0 1 945

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.42..8.10 rows=1 width=20) (actual time=0.042..0.042 rows=1 loops=945)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
  • Filter: (visit_type = 'i'::bpchar)
16. 0.002 0.080 ↑ 1.0 1 1

Hash (cost=16.90..16.90 rows=1 width=12) (actual time=0.080..0.080 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.006 0.078 ↑ 1.0 1 1

Nested Loop (cost=0.85..16.90 rows=1 width=12) (actual time=0.076..0.078 rows=1 loops=1)

18. 0.041 0.041 ↑ 1.0 1 1

Index Scan using bill_pkey on bill b (cost=0.43..8.45 rows=1 width=14) (actual time=0.040..0.041 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = '18/19MSL0005283'::text)
19. 0.031 0.031 ↑ 1.0 1 1

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..8.45 rows=1 width=20) (actual time=0.030..0.031 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)