explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nXd

Settings
# exclusive inclusive rows x rows loops node
1. 4,572.282 136,221.083 ↓ 37.8 2,838,252 1

Finalize GroupAggregate (cost=2,098,239.77..2,108,614.05 rows=75,029 width=97) (actual time=121,814.797..136,221.083 rows=2,838,252 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
2. 0.000 131,648.801 ↓ 69.9 4,368,543 1

Gather Merge (cost=2,098,239.77..2,106,394.45 rows=62,524 width=97) (actual time=121,814.786..131,648.801 rows=4,368,543 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 9,376.008 381,593.802 ↓ 46.6 1,456,181 3

Partial GroupAggregate (cost=2,097,239.74..2,098,177.60 rows=31,262 width=97) (actual time=120,411.752..127,197.934 rows=1,456,181 loops=3)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
4. 75,448.539 372,217.794 ↓ 159.7 4,993,431 3

Sort (cost=2,097,239.74..2,097,317.90 rows=31,262 width=41) (actual time=120,411.730..124,072.598 rows=4,993,431 loops=3)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: external merge Disk: 261792kB
5. 10,474.359 296,769.255 ↓ 159.7 4,993,431 3

Nested Loop Left Join (cost=1,458,203.91..2,094,905.70 rows=31,262 width=41) (actual time=27,108.308..98,923.085 rows=4,993,431 loops=3)

6. 49,017.612 151,472.772 ↓ 159.7 4,993,412 3

Hash Join (cost=1,458,203.48..2,079,351.96 rows=31,262 width=37) (actual time=27,107.211..50,490.924 rows=4,993,412 loops=3)

  • Hash Cond: (((bcc.charge_id)::text = (bc.charge_id)::text) AND ("substring"((bcc.bill_no)::text, 5, 2) = "substring"((bc.bill_no)::text, 5, 2)))
7. 21,168.222 21,168.222 ↑ 1.3 5,286,784 3

Parallel Seq Scan on bill_charge_claim bcc (cost=0.00..369,702.05 rows=6,616,805 width=47) (actual time=0.179..7,056.074 rows=5,286,784 loops=3)

8. 20,346.990 81,286.938 ↑ 1.0 16,808,552 3

Hash (cost=1,106,683.76..1,106,683.76 rows=16,851,848 width=24) (actual time=27,095.646..27,095.646 rows=16,808,552 loops=3)

  • Buckets: 2097152 Batches: 16 Memory Usage: 75332kB
9. 60,939.948 60,939.948 ↑ 1.0 16,808,552 3

Seq Scan on bill_charge bc (cost=0.00..1,106,683.76 rows=16,851,848 width=24) (actual time=0.037..20,313.316 rows=16,808,552 loops=3)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 974336
10. 134,822.124 134,822.124 ↑ 1.0 1 14,980,236

Index Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..0.49 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=14,980,236)

  • Index Cond: ((bill_no)::text = (bcc.bill_no)::text)
  • Filter: (((claim_id)::text = (bcc.claim_id)::text) AND ("substring"((bill_no)::text, 5, 2) = "substring"((bcc.bill_no)::text, 5, 2)))