explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iaF2

Settings
# exclusive inclusive rows x rows loops node
1. 0.401 1,975.279 ↓ 0.0 0 1

Hash Join (cost=153,705.90..172,345.07 rows=1 width=109) (actual time=1,975.279..1,975.279 rows=0 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
  • Total runtime: 1975.589 ms
2. 1.122 1,974.786 ↓ 1.4 1,123 1

Hash Left Join (cost=153,688.99..172,325.19 rows=787 width=106) (actual time=1,931.918..1,974.786 rows=1,123 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (setoffs.mr_no)::text)
3. 3.465 52.354 ↓ 1.4 1,123 1

HashAggregate (cost=440.95..19,063.73 rows=787 width=10) (actual time=10.588..52.354 rows=1,123 loops=1)

4. 9.584 9.584 ↓ 1.0 1,295 1

Seq Scan on patient_deposits pd (cost=0.00..437.76 rows=1,273 width=10) (actual time=0.969..9.584 rows=1,295 loops=1)

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

SubPlan (forHashAggregate)

6. 2.246 21.337 ↑ 1.0 1 1,123

Aggregate (cost=11.82..11.83 rows=1 width=5) (actual time=0.018..0.019 rows=1 loops=1,123)

7. 4.492 19.091 ↑ 1.0 1 1,123

Bitmap Heap Scan on patient_deposits pas (cost=4.30..11.81 rows=1 width=5) (actual time=0.016..0.017 rows=1 loops=1,123)

  • Recheck Cond: ((mr_no)::text = (pd.mr_no)::text)
  • Filter: ((realized = 'Y'::bpchar) AND ((deposit_available_for)::text = 'I'::text))
  • Rows Removed by Filter: 0
8. 14.599 14.599 ↑ 1.0 2 1,123

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.013..0.013 rows=2 loops=1,123)

  • Index Cond: ((mr_no)::text = (pd.mr_no)::text)
9. 1.123 17.968 ↑ 1.0 1 1,123

Aggregate (cost=11.82..11.83 rows=1 width=5) (actual time=0.016..0.016 rows=1 loops=1,123)

10. 3.369 16.845 ↓ 0.0 0 1,123

Bitmap Heap Scan on patient_deposits pds (cost=4.30..11.81 rows=1 width=5) (actual time=0.015..0.015 rows=0 loops=1,123)

  • Recheck Cond: ((mr_no)::text = (pd.mr_no)::text)
  • Filter: ((realized <> 'Y'::bpchar) AND ((deposit_available_for)::text = 'I'::text))
  • Rows Removed by Filter: 2
11. 13.476 13.476 ↑ 1.0 2 1,123

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.012..0.012 rows=2 loops=1,123)

  • Index Cond: ((mr_no)::text = (pd.mr_no)::text)
12. 0.496 1,921.310 ↓ 14.2 938 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
13. 0.405 1,920.814 ↓ 14.2 938 1

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

14. 1.861 1,920.409 ↓ 14.2 938 1

HashAggregate (cost=153,245.90..153,246.56 rows=66 width=12) (actual time=1,919.908..1,920.409 rows=938 loops=1)

15. 1.516 1,918.548 ↓ 14.3 945 1

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

16. 1,888.682 1,888.682 ↓ 1.0 945 1

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

  • Filter: (ip_deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 1538976
17. 28.350 28.350 ↑ 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.029..0.030 rows=1 loops=945)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
  • Filter: (visit_type = 'i'::bpchar)
18. 0.003 0.092 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.005 0.089 ↑ 1.0 1 1

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

20. 0.054 0.054 ↑ 1.0 1 1

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

  • Index Cond: ((bill_no)::text = '18/19MSL0005283'::text)
21. 0.030 0.030 ↑ 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.029..0.030 rows=1 loops=1)