explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j3N

Settings
# exclusive inclusive rows x rows loops node
1. 5,395.501 222,071.223 ↓ 2,838,240.0 2,838,240 1

GroupAggregate (cost=1,990,788.97..1,990,789.00 rows=1 width=97) (actual time=208,357.763..222,071.223 rows=2,838,240 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
2. 49,730.810 216,675.722 ↓ 14,980,153.0 14,980,153 1

Sort (cost=1,990,788.97..1,990,788.98 rows=1 width=41) (actual time=208,357.740..216,675.722 rows=14,980,153 loops=1)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: external merge Disk: 790968kB
3. 2,250.948 166,944.912 ↓ 14,980,153.0 14,980,153 1

Gather (cost=1,839,508.10..1,990,788.96 rows=1 width=41) (actual time=47,996.112..166,944.912 rows=14,980,153 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 102,925.479 164,693.964 ↓ 4,993,384.0 4,993,384 3

Nested Loop (cost=1,838,508.10..1,989,788.86 rows=1 width=41) (actual time=47,986.721..164,693.964 rows=4,993,384 loops=3)

5. 4,191.322 61,768.466 ↓ 5,286,760.0 5,286,760 3

Merge Join (cost=1,838,507.54..1,989,787.86 rows=1 width=66) (actual time=47,985.301..61,768.466 rows=5,286,760 loops=3)

  • Merge Cond: ((("substring"((bcc.bill_no)::text, 5, 2)) = ("substring"((bcl.bill_no)::text, 5, 2))) AND ((bcc.bill_no)::text = (bcl.bill_no)::text) AND ((bcc.claim_id)::text = (bcl.claim_id)::text))
6. 31,185.565 36,007.737 ↑ 1.3 5,286,762 3

Sort (cost=1,322,814.62..1,339,356.15 rows=6,616,610 width=47) (actual time=29,413.309..36,007.737 rows=5,286,762 loops=3)

  • Sort Key: ("substring"((bcc.bill_no)::text, 5, 2)), bcc.bill_no, bcc.claim_id
  • Sort Method: external merge Disk: 319808kB
7. 4,822.172 4,822.172 ↑ 1.3 5,286,762 3

Parallel Seq Scan on bill_charge_claim bcc (cost=0.00..369,691.10 rows=6,616,610 width=47) (actual time=3.138..4,822.172 rows=5,286,762 loops=3)

8. 605.162 21,569.407 ↓ 2.3 6,748,942 3

Materialize (cost=515,692.92..530,558.96 rows=2,973,209 width=33) (actual time=18,571.982..21,569.407 rows=6,748,942 loops=3)

9. 16,863.668 20,964.245 ↓ 1.0 2,973,231 3

Sort (cost=515,692.92..523,125.94 rows=2,973,209 width=33) (actual time=18,571.977..20,964.245 rows=2,973,231 loops=3)

  • Sort Key: ("substring"((bcl.bill_no)::text, 5, 2)), bcl.bill_no, bcl.claim_id
  • Sort Method: external merge Disk: 141344kB
10. 4,100.577 4,100.577 ↓ 1.0 2,973,241 3

Seq Scan on bill_claim bcl (cost=0.00..114,718.09 rows=2,973,209 width=33) (actual time=0.274..4,100.577 rows=2,973,241 loops=3)

11. 0.019 0.019 ↑ 1.0 1 15,860,280

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..0.99 rows=1 width=24) (actual time=0.019..0.019 rows=1 loops=15,860,280)

  • Index Cond: ((charge_id)::text = (bcc.charge_id)::text)
  • Filter: ((status <> 'X'::bpchar) AND ("substring"((bcc.bill_no)::text, 5, 2) = "substring"((bill_no)::text, 5, 2)))
  • Rows Removed by Filter: 0