explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j1pc

Settings
# exclusive inclusive rows x rows loops node
1. 97.747 197,649.380 ↑ 1.0 1 1

Merge Join (cost=6,919,792.70..7,222,108.48 rows=1 width=11) (actual time=197,649.371..197,649.380 rows=1 loops=1)

  • Merge Cond: (((bcc.claim_activity_id)::text = (rsd.group_charge)::text) AND ((bcc.claim_id)::text = (rsd.claim_id)::text))
  • Join Filter: ((sum((bc.act_quantity + bc.return_qty))) <> rsd.quantity)
  • Rows Removed by Join Filter: 1
2. 617.944 197,549.750 ↑ 4.5 493,127 1

GroupAggregate (cost=6,918,743.01..7,188,024.22 rows=2,202,293 width=32) (actual time=192,770.394..197,549.750 rows=493,127 loops=1)

3. 105,995.094 196,931.806 ↑ 12.4 1,595,492 1

Sort (cost=6,918,743.01..6,968,194.66 rows=19,780,663 width=32) (actual time=192,770.377..196,931.806 rows=1,595,492 loops=1)

  • Sort Key: bcc.claim_activity_id, bcc.claim_id
  • Sort Method: external merge Disk: 884520kB
4. 40,990.404 90,936.712 ↓ 1.1 20,778,156 1

Hash Join (cost=1,117,966.79..4,048,295.26 rows=19,780,663 width=32) (actual time=19,112.718..90,936.712 rows=20,778,156 loops=1)

  • Hash Cond: ((bc.charge_id)::text = (bcc.charge_id)::text)
5. 30,834.603 30,834.603 ↓ 1.0 23,415,479 1

Seq Scan on bill_charge bc (cost=0.00..1,630,091.96 rows=22,301,096 width=17) (actual time=0.301..30,834.603 rows=23,415,479 loops=1)

  • Filter: ((act_quantity > 0::numeric) AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 1401406
6. 5,412.970 19,111.705 ↓ 1.0 22,024,791 1

Hash (cost=670,625.24..670,625.24 rows=22,022,924 width=35) (actual time=19,111.705..19,111.705 rows=22,024,791 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 45945kB
7. 13,698.735 13,698.735 ↓ 1.0 22,024,791 1

Seq Scan on bill_charge_claim bcc (cost=0.00..670,625.24 rows=22,022,924 width=35) (actual time=0.023..13,698.735 rows=22,024,791 loops=1)

8. 0.015 1.883 ↑ 10.5 2 1

Sort (cost=1,049.70..1,049.75 rows=21 width=21) (actual time=1.882..1.883 rows=2 loops=1)

  • Sort Key: rsd.group_charge, rsd.claim_id
  • Sort Method: quicksort Memory: 25kB
9. 1.868 1.868 ↑ 10.5 2 1

Index Scan using idx_ra_stg_detials_file_id on ra_stg_details rsd (cost=0.56..1,049.23 rows=21 width=21) (actual time=1.397..1.868 rows=2 loops=1)

  • Index Cond: ((file_id)::text = 'T00000000074806'::text)
  • Filter: ((payment_amount > 0::numeric) AND ((charge_type)::text = 'A'::text) AND (is_combined = 'Y'::bpchar))
  • Rows Removed by Filter: 319