explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8vcQ : Optimization for: Optimization for: plan #J5VP; plan #dYEw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.452 550.942 ↓ 3.6 721 1

HashAggregate (cost=110,674.79..110,676.79 rows=200 width=15) (actual time=550.878..550.942 rows=721 loops=1)

2. 0.148 550.490 ↑ 3.5 1,958 1

Subquery Scan on foo (cost=110,521.88..110,657.80 rows=6,796 width=15) (actual time=550.038..550.490 rows=1,958 loops=1)

3. 1.230 550.342 ↑ 3.5 1,958 1

HashAggregate (cost=110,521.88..110,589.84 rows=6,796 width=62) (actual time=550.038..550.342 rows=1,958 loops=1)

4. 0.369 549.112 ↑ 3.5 1,958 1

Nested Loop (cost=88,628.92..110,453.92 rows=6,796 width=62) (actual time=530.941..549.112 rows=1,958 loops=1)

5. 4.931 536.995 ↑ 4.9 1,958 1

Hash Right Join (cost=88,628.49..89,211.63 rows=9,667 width=58) (actual time=530.914..536.995 rows=1,958 loops=1)

  • Hash Cond: ((br.bill_no)::text = (b.bill_no)::text)
  • Filter: (COALESCE((sum(br.allocated_amount)), 0::numeric) <> b.deposit_set_off)
  • Rows Removed by Filter: 6,677
6. 5.237 512.248 ↑ 2.3 7,425 1

HashAggregate (cost=87,335.99..87,508.58 rows=17,259 width=17) (actual time=511.040..512.248 rows=7,425 loops=1)

7. 3.055 507.011 ↑ 1.5 11,351 1

Nested Loop (cost=0.43..87,249.69 rows=17,259 width=17) (actual time=425.659..507.011 rows=11,351 loops=1)

8. 434.340 434.340 ↓ 1.0 17,404 1

Seq Scan on receipts r (cost=0.00..57,140.72 rows=17,323 width=12) (actual time=424.730..434.340 rows=17,404 loops=1)

  • Filter: (is_deposit AND (tpa_id IS NULL))
  • Rows Removed by Filter: 1,656,385
9. 69.616 69.616 ↑ 1.0 1 17,404

Index Scan using bill_receipts_receipt_no_index on bill_receipts br (cost=0.43..1.73 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=17,404)

  • Index Cond: ((receipt_no)::text = (r.receipt_id)::text)
10. 4.444 19.816 ↑ 1.1 8,635 1

Hash (cost=1,171.05..1,171.05 rows=9,716 width=26) (actual time=19.816..19.816 rows=8,635 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 531kB
11. 15.372 15.372 ↑ 1.1 8,635 1

Index Scan using idx_bill_deposit_set_off on bill b (cost=0.29..1,171.05 rows=9,716 width=26) (actual time=0.079..15.372 rows=8,635 loops=1)

  • Index Cond: (deposit_set_off > 0::numeric)
12. 11.748 11.748 ↑ 1.0 1 1,958

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..2.19 rows=1 width=27) (actual time=0.006..0.006 rows=1 loops=1,958)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
Total runtime : 551.244 ms