explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OKDe

Settings
# exclusive inclusive rows x rows loops node
1. 41.238 207,618.186 ↓ 3,291.0 3,291 1

GroupAggregate (cost=16,759.72..16,759.77 rows=1 width=272) (actual time=207,558.649..207,618.186 rows=3,291 loops=1)

  • Group Key: (COALESCE(bank_codes.bank_code, ("left"((payout_events.description)::text, 3))::character varying)), bank_codes.bank_name, payout_events.file_name, merchant_payouts.cip_nu_liquid, payout_events.net, card_types.name, merchant_payouts.id, mer
2.          

CTE merchant_payouts

3. 480.459 1,022.119 ↓ 183.2 162,310 1

GroupAggregate (cost=16,165.33..16,183.05 rows=886 width=70) (actual time=434.070..1,022.119 rows=162,310 loops=1)

  • Group Key: merchant_payouts_1.id
4. 222.505 541.660 ↓ 183.2 162,310 1

Sort (cost=16,165.33..16,167.55 rows=886 width=44) (actual time=434.044..541.660 rows=162,310 loops=1)

  • Sort Key: merchant_payouts_1.id
  • Sort Method: quicksort Memory: 18826kB
5. 155.462 319.155 ↓ 183.2 162,310 1

Nested Loop (cost=2.19..16,121.96 rows=886 width=44) (actual time=1.044..319.155 rows=162,310 loops=1)

6. 0.176 1.661 ↓ 76.0 76 1

Hash Join (cost=1.62..11.17 rows=1 width=4) (actual time=0.606..1.661 rows=76 loops=1)

  • Hash Cond: ((payout_events_1.payout_zone_code)::text = (payout_zones_1.zone_code)::text)
7. 1.467 1.467 ↓ 7.2 93 1

Index Scan using payout_events_created_at_date_idx on payout_events payout_events_1 (cost=0.29..9.80 rows=13 width=17) (actual time=0.420..1.467 rows=93 loops=1)

  • Index Cond: ((created_at)::date = '2019-01-31'::date)
8. 0.006 0.018 ↑ 1.0 1 1

Hash (cost=1.32..1.32 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on payout_zones payout_zones_1 (cost=0.00..1.32 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1)

  • Filter: (id = 83)
  • Rows Removed by Filter: 25
10. 162.032 162.032 ↑ 4.0 2,136 76

Index Scan using merchant_payouts_payout_event_id_merchant_id_idx on merchant_payouts merchant_payouts_1 (cost=0.56..16,025.51 rows=8,528 width=44) (actual time=0.208..2.132 rows=2,136 loops=76)

  • Index Cond: (payout_event_id = payout_events_1.id)
11. 252.327 207,576.948 ↓ 41,828.0 41,828 1

Sort (cost=576.67..576.67 rows=1 width=181) (actual time=207,558.606..207,576.948 rows=41,828 loops=1)

  • Sort Key: (COALESCE(bank_codes.bank_code, ("left"((payout_events.description)::text, 3))::character varying)), bank_codes.bank_name, payout_events.file_name, merchant_payouts.cip_nu_liquid, payout_events.net, card_types.name, merchant_payouts.id
  • Sort Method: quicksort Memory: 12130kB
12. 6,937.301 207,324.621 ↓ 41,828.0 41,828 1

Nested Loop Left Join (cost=11.10..576.66 rows=1 width=181) (actual time=3,171.519..207,324.621 rows=41,828 loops=1)

  • Join Filter: ((bank_codes.bank_code)::text = (COALESCE(payout_events.bank_code, ("left"((payout_events.description)::text, 3))::character varying))::text)
  • Rows Removed by Join Filter: 12966680
13. 9,849.622 193,820.324 ↓ 41,828.0 41,828 1

Nested Loop (cost=11.10..558.89 rows=1 width=157) (actual time=3,169.862..193,820.324 rows=41,828 loops=1)

  • Join Filter: (payout_events.id = tes.payout_event_id)
  • Rows Removed by Join Filter: 9511258
14. 3,078.996 67,006.189 ↓ 713,198.2 2,852,793 1

Nested Loop (cost=10.52..173.82 rows=4 width=169) (actual time=435.259..67,006.189 rows=2,852,793 loops=1)

15. 957.003 4,359.423 ↓ 162,310.0 162,310 1

Nested Loop (cost=9.96..32.77 rows=1 width=159) (actual time=434.282..4,359.423 rows=162,310 loops=1)

  • Join Filter: (merchant_payouts.card_type_id = card_types.id)
  • Rows Removed by Join Filter: 1319518
16. 387.955 2,590.870 ↓ 162,310.0 162,310 1

Nested Loop (cost=9.96..31.35 rows=1 width=157) (actual time=434.254..2,590.870 rows=162,310 loops=1)

  • Join Filter: ((payout_events.payout_zone_code)::text = (payout_zones.zone_code)::text)
17. 253.320 1,553.675 ↓ 162,310.0 162,310 1

Hash Join (cost=9.96..30.01 rows=1 width=170) (actual time=434.241..1,553.675 rows=162,310 loops=1)

  • Hash Cond: (merchant_payouts.payout_event_id = payout_events.id)
18. 1,300.205 1,300.205 ↓ 183.2 162,310 1

CTE Scan on merchant_payouts (cost=0.00..17.72 rows=886 width=80) (actual time=434.074..1,300.205 rows=162,310 loops=1)

19. 0.063 0.150 ↓ 7.2 93 1

Hash (cost=9.80..9.80 rows=13 width=90) (actual time=0.149..0.150 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
20. 0.087 0.087 ↓ 7.2 93 1

Index Scan using payout_events_created_at_date_idx on payout_events (cost=0.29..9.80 rows=13 width=90) (actual time=0.013..0.087 rows=93 loops=1)

  • Index Cond: ((created_at)::date = '2019-01-31'::date)
21. 649.240 649.240 ↑ 1.0 1 162,310

Seq Scan on payout_zones (cost=0.00..1.32 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=162,310)

  • Filter: (id = 83)
  • Rows Removed by Filter: 25
22. 811.550 811.550 ↑ 2.1 9 162,310

Seq Scan on card_types (cost=0.00..1.19 rows=19 width=10) (actual time=0.001..0.005 rows=9 loops=162,310)

23. 59,567.770 59,567.770 ↑ 2.1 18 162,310

Index Scan using transaction_events_merchant_id_transaction_id_idx on transaction_events te (cost=0.56..140.67 rows=37 width=14) (actual time=0.139..0.367 rows=18 loops=162,310)

  • Index Cond: (merchant_id = merchant_payouts.merchant_id)
  • Filter: (current_status_id = ANY ('{17,20,21,22,23}'::integer[]))
  • Rows Removed by Filter: 2
24. 116,964.513 116,964.513 ↑ 18.0 3 2,852,793

Index Scan using transaction_event_states_transaction_event_id_transaction_e_idx on transaction_event_states tes (cost=0.58..95.59 rows=54 width=8) (actual time=0.028..0.041 rows=3 loops=2,852,793)

  • Index Cond: (transaction_event_id = te.id)
25. 6,566.996 6,566.996 ↑ 1.0 311 41,828

Seq Scan on bank_codes (cost=0.00..13.10 rows=311 width=29) (actual time=0.002..0.157 rows=311 loops=41,828)

  • Filter: (country_id = 45)
  • Rows Removed by Filter: 17