explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 48FP : Optimization for: plan #dQXr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 382.301 293,303.616 ↓ 3.5 247,844 1

Sort (cost=47,477,765.42..47,477,944.94 rows=71,808 width=688) (actual time=293,219.704..293,303.616 rows=247,844 loops=1)

  • Sort Key: (COALESCE(mt.refund_date, mt.capture_date)) DESC
  • Sort Method: external merge Disk: 70,360kB
2. 234.634 292,921.315 ↓ 3.5 247,844 1

Unique (cost=47,444,920.36..47,450,126.44 rows=71,808 width=688) (actual time=292,214.763..292,921.315 rows=247,844 loops=1)

3. 2,294.498 292,686.681 ↓ 3.5 247,844 1

Sort (cost=47,444,920.36..47,445,099.88 rows=71,808 width=688) (actual time=292,214.760..292,686.681 rows=247,844 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: 70,360kB
4. 62.688 290,392.183 ↓ 3.5 247,844 1

Append (cost=1.27..47,417,281.37 rows=71,808 width=688) (actual time=43,312.302..290,392.183 rows=247,844 loops=1)

5. 27.457 43,301.286 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.27..43,027,540.55 rows=1 width=518) (actual time=43,301.286..43,301.286 rows=0 loops=1)

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

Index Scan using mood_transactions_merchant_id_event_date_index on mood_transactions mt (cost=0.57..48,723.06 rows=14,207 width=294) (actual time=3.851..297.506 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. 23.698 42,976.323 ↑ 1.0 1 11,849

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

8. 42,952.625 42,952.625 ↑ 1.0 1 11,849

Index Scan using transactions_order_id_idx on transactions (cost=0.70..3,025.17 rows=1 width=556) (actual time=3.625..3.625 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. 2,625.018 247,028.209 ↓ 3.5 247,844 1

Nested Loop Left Join (cost=1.71..4,389,022.75 rows=71,807 width=444) (actual time=11.015..247,028.209 rows=247,844 loops=1)

10. 471.360 243,163.971 ↓ 3.5 247,844 1

Nested Loop Left Join (cost=1.27..3,785,922.44 rows=71,807 width=345) (actual time=7.654..243,163.971 rows=247,844 loops=1)

11. 2,283.931 2,283.931 ↓ 3.5 247,844 1

Index Scan using transactions_merchant_id_event_date_index on transactions transactions_1 (cost=0.57..246,278.47 rows=71,807 width=224) (actual time=5.302..2,283.931 rows=247,844 loops=1)

  • Index Cond: ((merchant_id = ANY ('{K506531}'::text[])) AND (event_date >= '2020-08-19 00:00:00+00'::timestamp with time zone) AND (event_date <= '2020-08-20 00:00:00+00'::timestamp with time zone))
12. 247.844 240,408.680 ↑ 1.0 1 247,844

Limit (cost=0.70..49.27 rows=1 width=401) (actual time=0.970..0.970 rows=1 loops=247,844)

13. 240,160.836 240,160.836 ↑ 1.0 1 247,844

Index Scan using mood_transactions_order_id_idx on mood_transactions (cost=0.70..49.27 rows=1 width=401) (actual time=0.969..0.969 rows=1 loops=247,844)

  • 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. 1,239.220 1,239.220 ↑ 1.0 1 247,844

Index Scan using settlements_pkey on settlements (cost=0.44..7.88 rows=1 width=59) (actual time=0.005..0.005 rows=1 loops=247,844)

  • Index Cond: (transactions_1.settlement_id = settlement_id)
Planning time : 2.797 ms
Execution time : 293,334.823 ms