explain.depesz.com

PostgreSQL's explain analyze made readable

Result: opej : Optimization for: plan #fn3W

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=22,308.02..22,427.31 rows=450 width=202) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=22,308.02..22,313.44 rows=2,169 width=202) (actual rows= loops=)

  • Sort Key: ic.pymntgw_id, ic.receiptid, ic.referencenum, ic.responsecode, ic.iso, ic.authcode, ic.transtime, ic.transdate, ic.transtype, ic.complete, ic.message, ic.transamount, ic.cardtype, ic.txnnumber, ic.timedout, ic.datakey, ic.ressuccess, ic.paymenttype, ic.cuser, ic.cdate, ic.maskedpan
3. 0.000 0.000 ↓ 0.0

Gather (cost=18,568.12..22,187.83 rows=2,169 width=202) (actual rows= loops=)

  • Workers Planned: 1
4. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=17,568.12..20,970.93 rows=1,276 width=202) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17,567.70..19,951.69 rows=1,586 width=202) (actual rows= loops=)

  • Join Filter: (ar.pymntgw_id = ic.pymntgw_id)
6. 0.000 0.000 ↓ 0.0

Merge Join (cost=17,567.41..18,569.72 rows=1,053 width=10) (actual rows= loops=)

  • Merge Cond: ((dtl.pymntgw_id = ar.pymntgw_id) AND (dtl.acct_bill_id = ar.acct_bill_id))
7. 0.000 0.000 ↓ 0.0

Sort (cost=10,915.77..11,170.00 rows=101,694 width=11) (actual rows= loops=)

  • Sort Key: dtl.pymntgw_id, dtl.acct_bill_id
8. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pymntgw_og_dtl dtl (cost=0.00..2,457.94 rows=101,694 width=11) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=6,651.34..6,745.44 rows=37,640 width=12) (actual rows= loops=)

  • Sort Key: ar.pymntgw_id, ar.acct_bill_id
10. 0.000 0.000 ↓ 0.0

Seq Scan on pymntgw_ic_ar ar (cost=0.00..3,790.70 rows=37,640 width=12) (actual rows= loops=)

  • Filter: ((acct_bill_id IS NOT NULL) AND (COALESCE(res_txn_id, '-1'::numeric) < '0'::numeric))
11. 0.000 0.000 ↓ 0.0

Index Scan using pymntgwic_idx01 on pymntgw_ic ic (cost=0.29..1.30 rows=1 width=202) (actual rows= loops=)

  • Index Cond: (pymntgw_id = dtl.pymntgw_id)
  • Filter: ((to_number((COALESCE(responsecode, '999'::character varying))::text, '000'::text) >= '0'::numeric) AND (to_number((COALESCE(responsecode, '999'::character varying))::text, '000'::text) <= '49'::numeric))
12. 0.000 0.000 ↓ 0.0

Index Scan using pymntgwicar_idx01 on pymntgw_ic_ar s (cost=0.42..29.20 rows=118 width=5) (actual rows= loops=)

  • Index Cond: (pymntgw_id = ic.pymntgw_id)
  • Filter: (COALESCE(res_txn_id, '-1'::numeric) > '0'::numeric)