explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J5VP

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.213 1,955.003 ↑ 3.8 1,958 1

HashAggregate (cost=464,791.85..464,865.83 rows=7,398 width=62) (actual time=1,954.717..1,955.003 rows=1,958 loops=1)

2. 0.300 1,953.790 ↑ 3.8 1,958 1

Nested Loop (cost=441,386.35..464,717.87 rows=7,398 width=62) (actual time=1,936.212..1,953.790 rows=1,958 loops=1)

3. 4.394 1,941.742 ↑ 5.4 1,958 1

Hash Right Join (cost=441,385.92..441,882.82 rows=10,523 width=58) (actual time=1,936.185..1,941.742 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.396 471.924 ↑ 2.3 7,425 1

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

5. 3.291 466.528 ↑ 1.5 11,351 1

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

6. 393.621 393.621 ↓ 1.0 17,404 1

Seq Scan on receipts r (cost=0.00..57,140.72 rows=17,323 width=12) (actual time=384.087..393.621 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. 2.183 1,465.424 ↑ 1.2 8,635 1

Hash (cost=353,917.73..353,917.73 rows=10,576 width=26) (actual time=1,465.424..1,465.424 rows=8,635 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 531kB
9. 1,463.241 1,463.241 ↑ 1.2 8,635 1

Seq Scan on bill b (cost=0.00..353,917.73 rows=10,576 width=26) (actual time=1.772..1,463.241 rows=8,635 loops=1)

  • Filter: ((deposit_set_off > 0::numeric) OR (ip_deposit_set_off > 0::numeric))
  • Rows Removed by Filter: 3,230,212
10. 11.748 11.748 ↑ 1.0 1 1,958

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..2.16 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 : 1,955.265 ms