explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nVO

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 10,204.518 ↓ 2.5 78 1

Merge Append (cost=21,426.79..21,429.04 rows=31 width=199) (actual time=10,002.500..10,204.518 rows=78 loops=1)

  • Sort Key: ic.pymntgw_id
  • Buffers: shared hit=280318, temp read=623 written=623
2. 133.130 3,710.135 ↑ 10.0 3 1

Unique (cost=14,700.34..14,701.99 rows=30 width=199) (actual time=3,557.136..3,710.135 rows=3 loops=1)

  • Buffers: shared hit=36241, temp read=623 written=623
3. 2,297.010 3,577.005 ↓ 675.3 20,259 1

Sort (cost=14,700.34..14,700.42 rows=30 width=199) (actual time=3,557.133..3,577.005 rows=20,259 loops=1)

  • 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
  • Sort Method: external sort Disk: 4984kB
  • Buffers: shared hit=36241, temp read=623 written=623
4. 40.517 1,279.995 ↓ 675.3 20,259 1

Gather (cost=5,185.68..14,699.61 rows=30 width=199) (actual time=856.897..1,279.995 rows=20,259 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=36235
5. 146.789 1,239.478 ↓ 562.8 6,753 3

Merge Join (cost=4,185.68..13,696.61 rows=12 width=199) (actual time=1,123.419..1,239.478 rows=6,753 loops=3)

  • Merge Cond: (dtl.pymntgw_id = ic.pymntgw_id)
  • Join Filter: (ar.txn_id = dtl.txn_id)
  • Buffers: shared hit=36235
6. 17.189 17.189 ↑ 108.8 9,835 3

Parallel Index Scan using pymntgwogdtl_idx01 on pymntgw_og_dtl dtl (cost=0.43..139,182.12 rows=1,069,558 width=8) (actual time=0.008..17.189 rows=9,835 loops=3)

  • Buffers: shared hit=6360
7. 135.586 1,075.500 ↓ 34.0 20,259 3

Materialize (cost=4,181.61..4,224.18 rows=595 width=211) (actual time=823.396..1,075.500 rows=20,259 loops=3)

  • Buffers: shared hit=29875
8. 103.988 939.914 ↓ 34.0 20,259 3

Merge Join (cost=4,181.61..4,222.69 rows=595 width=211) (actual time=823.391..939.914 rows=20,259 loops=3)

  • Merge Cond: (ic.pymntgw_id = ar.pymntgw_id)
  • Buffers: shared hit=29875
9. 0.374 616.375 ↑ 1.4 140 3

Sort (cost=2,698.45..2,698.94 rows=197 width=199) (actual time=616.314..616.375 rows=140 loops=3)

  • Sort Key: ic.pymntgw_id
  • Sort Method: quicksort Memory: 64kB
  • Buffers: shared hit=27637
10. 190.399 616.001 ↑ 1.4 145 3

Nested Loop Anti Join (cost=0.29..2,690.94 rows=197 width=199) (actual time=0.033..616.001 rows=145 loops=3)

  • Buffers: shared hit=27629
11. 425.481 425.481 ↓ 4.9 1,378 3

Seq Scan on pymntgw_ic ic (cost=0.00..2,250.98 rows=281 width=199) (actual time=0.017..425.481 rows=1,378 loops=3)

  • 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))
  • Rows Removed by Filter: 54423
  • Buffers: shared hit=3384
12. 0.121 0.121 ↑ 12.0 1 4,134

Index Scan using pymntgwicar_idx01 on pymntgw_ic_ar s (cost=0.29..12.44 rows=12 width=5) (actual time=0.121..0.121 rows=1 loops=4,134)

  • Index Cond: (pymntgw_id = ic.pymntgw_id)
  • Filter: (COALESCE(res_txn_id, '-1'::numeric) > '0'::numeric)
  • Rows Removed by Filter: 24
  • Buffers: shared hit=24245
13. 138.268 219.551 ↓ 3.0 20,259 3

Sort (cost=1,483.17..1,500.24 rows=6,830 width=12) (actual time=200.264..219.551 rows=20,259 loops=3)

  • Sort Key: ar.pymntgw_id
  • Sort Method: quicksort Memory: 1718kB
  • Buffers: shared hit=2238
14. 81.283 81.283 ↓ 3.0 20,259 3

Seq Scan on pymntgw_ic_ar ar (cost=0.00..1,048.17 rows=6,830 width=12) (actual time=0.083..81.283 rows=20,259 loops=3)

  • Filter: ((txn_id IS NOT NULL) AND (COALESCE(res_txn_id, '-1'::numeric) < '0'::numeric))
  • Rows Removed by Filter: 3575
  • Buffers: shared hit=2238
15. 45.359 6,494.295 ↓ 75.0 75 1

Unique (cost=6,726.44..6,726.50 rows=1 width=199) (actual time=6,445.360..6,494.295 rows=75 loops=1)

  • Buffers: shared hit=244077
16. 372.322 6,448.936 ↓ 9,084.0 9,084 1

Sort (cost=6,726.44..6,726.45 rows=1 width=199) (actual time=6,445.357..6,448.936 rows=9,084 loops=1)

  • Sort Key: ic_1.pymntgw_id, ic_1.receiptid, ic_1.referencenum, ic_1.responsecode, ic_1.iso, ic_1.authcode, ic_1.transtime, ic_1.transdate, ic_1.transtype, ic_1.complete, ic_1.message, ic_1.transamount, ic_1.cardtype, ic_1.txnnumber, ic_1.timedout, ic_1.datakey, ic_1.ressuccess, ic_1.paymenttype, ic_1.cuser, ic_1.cdate, ic_1.maskedpan
  • Sort Method: quicksort Memory: 2746kB
  • Buffers: shared hit=244077
17. 2,962.819 6,076.614 ↓ 9,084.0 9,084 1

Gather (cost=3,730.67..6,726.43 rows=1 width=199) (actual time=1,865.587..6,076.614 rows=9,084 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=244077
18. 287.138 3,113.795 ↓ 4,542.0 4,542 2

Nested Loop (cost=2,730.67..5,726.33 rows=1 width=199) (actual time=1,011.575..3,113.795 rows=4,542 loops=2)

  • Join Filter: (ic_1.pymntgw_id = dtl_1.pymntgw_id)
  • Buffers: shared hit=244077
19. 2,588.131 2,826.600 ↓ 72.1 4,542 2

Nested Loop Anti Join (cost=2,730.24..2,877.51 rows=63 width=211) (actual time=1,011.542..2,826.600 rows=4,542 loops=2)

  • Buffers: shared hit=204716
20. 64.631 238.053 ↓ 68.5 6,097 2

Merge Join (cost=2,729.96..2,737.81 rows=89 width=211) (actual time=168.410..238.053 rows=6,097 loops=2)

  • Merge Cond: (ic_1.pymntgw_id = ar_1.pymntgw_id)
  • Buffers: shared hit=1321
21. 7.315 153.465 ↓ 3.0 491 2

Sort (cost=1,794.65..1,795.07 rows=165 width=199) (actual time=153.250..153.465 rows=491 loops=2)

  • Sort Key: ic_1.pymntgw_id
  • Sort Method: quicksort Memory: 220kB
  • Buffers: shared hit=1132
22. 146.150 146.150 ↓ 4.2 689 2

Parallel Seq Scan on pymntgw_ic ic_1 (cost=0.00..1,788.58 rows=165 width=199) (actual time=0.387..146.150 rows=689 loops=2)

  • 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))
  • Rows Removed by Filter: 27212
  • Buffers: shared hit=1128
23. 5.910 19.957 ↓ 5.5 6,696 2

Sort (cost=935.30..938.37 rows=1,228 width=12) (actual time=14.989..19.957 rows=6,696 loops=2)

  • Sort Key: ar_1.pymntgw_id
  • Sort Method: quicksort Memory: 103kB
  • Buffers: shared hit=189
24. 13.844 14.047 ↑ 1.1 1,152 2

Bitmap Heap Scan on pymntgw_ic_ar ar_1 (cost=80.23..872.29 rows=1,228 width=12) (actual time=0.221..14.047 rows=1,152 loops=2)

  • Recheck Cond: (acct_bill_id IS NOT NULL)
  • Filter: (COALESCE(res_txn_id, '-1'::numeric) < '0'::numeric)
  • Rows Removed by Filter: 2365
  • Heap Blocks: exact=82
  • Buffers: shared hit=189
25. 0.203 0.203 ↑ 1.0 3,517 2

Bitmap Index Scan on pymntgwicar_idx03 (cost=0.00..79.92 rows=3,685 width=0) (actual time=0.202..0.203 rows=3,517 loops=2)

  • Index Cond: (acct_bill_id IS NOT NULL)
  • Buffers: shared hit=25
26. 0.416 0.416 ↓ 0.0 0 12,194

Index Scan using pymntgwicar_idx01 on pymntgw_ic_ar s_1 (cost=0.29..12.44 rows=12 width=5) (actual time=0.416..0.416 rows=0 loops=12,194)

  • Index Cond: (pymntgw_id = ic_1.pymntgw_id)
  • Filter: (COALESCE(res_txn_id, '-1'::numeric) > '0'::numeric)
  • Rows Removed by Filter: 179
  • Buffers: shared hit=203395
27. 0.057 0.057 ↑ 1.0 1 9,084

Index Only Scan using pymntgwogdtl_idx01 on pymntgw_og_dtl dtl_1 (cost=0.43..45.21 rows=1 width=11) (actual time=0.051..0.057 rows=1 loops=9,084)

  • Index Cond: ((pymntgw_id = ar_1.pymntgw_id) AND (acct_bill_id = ar_1.acct_bill_id))
  • Heap Fetches: 0
  • Buffers: shared hit=39361