explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dQXr

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27.969 1,099.523 ↓ 1.1 26,539 1

Sort (cost=53,716,050.20..53,716,109.52 rows=23,729 width=688) (actual time=1,092.563..1,099.523 rows=26,539 loops=1)

  • Sort Key: (COALESCE(mt.refund_date, mt.capture_date)) DESC
  • Sort Method: external merge Disk: 7,544kB
2. 22.520 1,071.554 ↓ 1.1 26,539 1

Unique (cost=53,705,384.92..53,707,105.27 rows=23,729 width=688) (actual time=1,025.473..1,071.554 rows=26,539 loops=1)

3. 155.902 1,049.034 ↓ 1.1 26,539 1

Sort (cost=53,705,384.92..53,705,444.24 rows=23,729 width=688) (actual time=1,025.472..1,049.034 rows=26,539 loops=1)

  • Sort Key: ('TRANSACTION_ROW'::text), ((mt.transaction_type)::text), mt.detailed_type, mt.sale_date, (COALESCE(mt.refund_date, mt.capture_date)), mt.order_id, mt.short_order_id, mt.capture_id, mt.merchant_reference1, mt.merchant_reference2, mt.merchant_capture_reference, mt.merchant_refund_reference, mt.amount, mt.currency_code, mt.refund_id, mt.purchase_country, mt.shipping_address_country, (NULL::integer), (NULL::numeric), mt.initial_payment_method_type, mt.initial_payment_method_number_of_installments, mt.initial_payment_method_monthly_downpayments, mt.merchant_id, (NULL::character varying), (NULL::text), (NULL::text), (NULL::text), (NULL::timestamp with time zone)
  • Sort Method: external merge Disk: 7,536kB
4. 3.850 893.132 ↓ 1.1 26,539 1

Append (cost=1.27..53,696,439.99 rows=23,729 width=688) (actual time=252.281..893.132 rows=26,539 loops=1)

5. 6.549 252.166 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.27..52,530,602.07 rows=1 width=520) (actual time=252.166..252.166 rows=0 loops=1)

  • Filter: (transactions.settlement_id IS NULL)
  • Rows Removed by Filter: 11,849
6. 8.637 8.637 ↑ 1.5 11,849 1

Index Scan using mood_transactions_merchant_id_event_date_index on mood_transactions mt (cost=0.57..59,324.56 rows=17,413 width=296) (actual time=0.033..8.637 rows=11,849 loops=1)

  • Index Cond: ((merchant_id = ANY ('{K506531}'::text[])) AND (event_date >= '2020-08-15 00:00:00+00'::timestamp with time zone) AND (event_date <= '2020-08-16 00:00:00+00'::timestamp with time zone))
7. 11.849 236.980 ↑ 1.0 1 11,849

Limit (cost=0.70..3,013.32 rows=1 width=556) (actual time=0.020..0.020 rows=1 loops=11,849)

8. 225.131 225.131 ↑ 1.0 1 11,849

Index Scan using transactions_order_id_idx on transactions (cost=0.70..3,013.32 rows=1 width=556) (actual time=0.019..0.019 rows=1 loops=11,849)

  • Index Cond: (mt.order_id = order_id)
  • Filter: ((refund_id = mt.refund_id) OR (capture_id = mt.capture_id))
  • Rows Removed by Filter: 2
9. 134.329 637.116 ↓ 1.1 26,539 1

Nested Loop Left Join (cost=1.71..1,165,600.63 rows=23,728 width=443) (actual time=0.115..637.116 rows=26,539 loops=1)

10. 12.581 449.709 ↓ 1.1 26,539 1

Nested Loop Left Join (cost=1.27..962,377.15 rows=23,728 width=344) (actual time=0.066..449.709 rows=26,539 loops=1)

11. 12.504 12.504 ↓ 1.1 26,539 1

Index Scan using transactions_merchant_id_event_date_index on transactions transactions_1 (cost=0.57..81,181.23 rows=23,728 width=221) (actual time=0.024..12.504 rows=26,539 loops=1)

  • Index Cond: ((merchant_id = ANY ('{K506531}'::text[])) AND (event_date >= '2020-08-15 00:00:00+00'::timestamp with time zone) AND (event_date <= '2020-08-16 00:00:00+00'::timestamp with time zone))
12. 26.539 424.624 ↑ 1.0 1 26,539

Limit (cost=0.70..37.12 rows=1 width=403) (actual time=0.015..0.016 rows=1 loops=26,539)

13. 398.085 398.085 ↑ 1.0 1 26,539

Index Scan using mood_transactions_order_id_idx on mood_transactions (cost=0.70..37.12 rows=1 width=403) (actual time=0.015..0.015 rows=1 loops=26,539)

  • Index Cond: (transactions_1.order_id = order_id)
  • Filter: (((transactions_1.refund_id = refund_id) OR (transactions_1.capture_id = capture_id)) AND CASE WHEN (transactions_1.transaction_type = 'COMMISSION'::text) THEN (transaction_type = 'SALE'::transaction_type) WHEN (transactions_1.transaction_type = 'FEE'::text) THEN (transaction_type = 'SALE'::transaction_type) WHEN (transactions_1.transaction_type = 'FEE_REFUND'::text) THEN (transaction_type = 'RETURN'::transaction_type) WHEN ((transactions_1.transaction_type = 'REVERSAL'::text) AND (transactions_1.detailed_type = 'COMMISSION_RETURN'::text)) THEN (transaction_type = 'RETURN'::transaction_type) ELSE (transactions_1.transaction_type = (transaction_type)::text) END)
  • Rows Removed by Filter: 1
14. 53.078 53.078 ↑ 1.0 1 26,539

Index Scan using settlements_pkey on settlements (cost=0.44..8.05 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=26,539)

  • Index Cond: (transactions_1.settlement_id = settlement_id)
Planning time : 0.918 ms
Execution time : 1,103.486 ms