explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.221 548.927 ↑ 3.5 1,958 1

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

2. 0.314 547.706 ↑ 3.5 1,958 1

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

3. 4.796 535.644 ↑ 4.9 1,958 1

Hash Right Join (cost=88,628.49..89,211.63 rows=9,667 width=58) (actual time=529.706..535.644 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
4. 5.252 511.136 ↑ 2.3 7,425 1

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

5. 3.264 505.884 ↑ 1.5 11,351 1

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

6. 433.004 433.004 ↓ 1.0 17,404 1

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

  • Filter: (is_deposit AND (tpa_id IS NULL))
  • Rows Removed by Filter: 1,656,385
7. 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)
8. 4.338 19.712 ↑ 1.1 8,635 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 531kB
9. 15.374 15.374 ↑ 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.052..15.374 rows=8,635 loops=1)

  • Index Cond: (deposit_set_off > 0::numeric)
10. 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 : 549.311 ms