explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SI1g

Settings
# exclusive inclusive rows x rows loops node
1. 5,327.885 664,215.463 ↓ 2,838,237.0 2,838,237 1

GroupAggregate (cost=1,990,788.97..1,990,789.00 rows=1 width=97) (actual time=650,346.460..664,215.463 rows=2,838,237 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Execution time: 664372.492 ms
2. 46,849.575 658,887.578 ↓ 14,980,139.0 14,980,139 1

Sort (cost=1,990,788.97..1,990,788.98 rows=1 width=41) (actual time=650,346.436..658,887.578 rows=14,980,139 loops=1)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: external merge Disk: 790960kB
3. 569.658 612,038.003 ↓ 14,980,139.0 14,980,139 1

Gather (cost=1,839,508.10..1,990,788.96 rows=1 width=41) (actual time=45,104.981..612,038.003 rows=14,980,139 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 542,186.965 611,468.345 ↓ 4,993,380.0 4,993,380 3

Nested Loop (cost=1,838,508.10..1,989,788.86 rows=1 width=41) (actual time=45,049.146..611,468.345 rows=4,993,380 loops=3)

5. 7,733.128 69,281.278 ↓ 5,286,755.0 5,286,755 3

Merge Join (cost=1,838,507.54..1,989,787.86 rows=1 width=66) (actual time=45,024.375..69,281.278 rows=5,286,755 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. 35,045.248 39,131.436 ↑ 1.3 5,286,757 3

Sort (cost=1,322,814.62..1,339,356.15 rows=6,616,610 width=47) (actual time=27,996.112..39,131.436 rows=5,286,757 loops=3)

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

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

8. 1,162.970 22,416.714 ↓ 2.3 6,732,645 3

Materialize (cost=515,692.92..530,558.96 rows=2,973,209 width=33) (actual time=17,028.253..22,416.714 rows=6,732,645 loops=3)

9. 18,377.295 21,253.744 ↓ 1.0 2,973,234 3

Sort (cost=515,692.92..523,125.94 rows=2,973,209 width=33) (actual time=17,028.248..21,253.744 rows=2,973,234 loops=3)

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

Seq Scan on bill_claim bcl (cost=0.00..114,718.09 rows=2,973,209 width=33) (actual time=0.837..2,876.449 rows=2,973,238 loops=3)

11. 0.102 0.102 ↑ 1.0 1 15,860,266

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

  • 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