explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7T3H

Settings

Optimization(s) for this plan:

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

Hash Left Join (cost=70,801,218.88..73,569,978.63 rows=9,199 width=320) (actual rows= loops=)

  • Hash Cond: (all_disputes.parent_id = braintree_chargeback_fees.processor_id)
2.          

CTE all_disputes

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=445,657.53..30,962,527.09 rows=1,839,799 width=398) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=445,541.03..12,690,015.50 rows=4,337 width=94) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=445,429.53..12,189,441.34 rows=4,337 width=102) (actual rows= loops=)

  • Hash Cond: (gd.processing_record_id = aib_chargebacks.id)
  • Join Filter: (gd.processing_record_type = 'AIB::Chargeback'::text)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=353,916.53..12,094,648.59 rows=4,337 width=122) (actual rows= loops=)

  • Hash Cond: (gt.merchant_account_id = ma.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=228.50..11,705,267.06 rows=44,700 width=115) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using index_gateway_disputes_on_kind_and_disbursement_date on gateway_disputes gd (cost=112.00..6,251,769.56 rows=44,700 width=84) (actual rows= loops=)

  • Index Cond: (disbursement_date = '2018-10-03'::date)
9. 0.000 0.000 ↓ 0.0

Index Scan using gateway_transactions_pkey on gateway_transactions gt (cost=116.50..121.50 rows=1 width=31) (actual rows= loops=)

  • Index Cond: (id = gd.gateway_transaction_id)
10. 0.000 0.000 ↓ 0.0

Hash (cost=216,570.03..216,570.03 rows=137,118 width=23) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using index_merchant_accounts_on_region on merchant_accounts ma (cost=85.50..216,570.03 rows=137,118 width=23) (actual rows= loops=)

  • Index Cond: (region = 'EUR'::bt_merchant_accounts_region_enum)
12. 0.000 0.000 ↓ 0.0

Hash (cost=33,023.00..33,023.00 rows=58,490 width=24) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on aib_chargebacks (cost=0.00..33,023.00 rows=58,490 width=24) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Only Scan using index_conversions_on_convertible_id_and_type on currency_conversion_results ccr (cost=111.50..114.92 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((convertable_id = gd.id) AND (convertable_type = 'Gateway::Dispute'::text))
15. 0.000 0.000 ↓ 0.0

Index Only Scan using index_gateway_transaction_fees_on_gateway_transaction_id on gateway_transaction_fees gtf (cost=116.50..607.49 rows=424 width=8) (actual rows= loops=)

  • Index Cond: (gateway_transaction_id = gd.gateway_transaction_id)
16.          

CTE braintree_chargeback_fees

17. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=39,838,268.79..39,838,503.79 rows=94 width=40) (actual rows= loops=)

  • Group Key: gateway_transactions.processor_id
18. 0.000 0.000 ↓ 0.0

Sort (cost=39,838,268.79..39,838,315.79 rows=94 width=16) (actual rows= loops=)

  • Sort Key: gateway_transactions.processor_id
19. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,750,052.60..39,837,652.66 rows=94 width=16) (actual rows= loops=)

  • Hash Cond: (gateway_transaction_fees.gateway_transaction_id = gateway_transactions.id)
20. 0.000 0.000 ↓ 0.0

Index Scan using index_gw_transaction_fees_on_gw_dispute_id_and_kind on gateway_transaction_fees (cost=87.00..20,373,929.05 rows=10,284,948 width=16) (actual rows= loops=)

  • Filter: ((promotional_credit_amount = 0) AND (kind = 'chargeback'::text))
21. 0.000 0.000 ↓ 0.0

Hash (cost=11,705,265.60..11,705,265.60 rows=44,700 width=24) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=228.50..11,705,265.60 rows=44,700 width=24) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using index_gateway_disputes_on_kind_and_disbursement_date on gateway_disputes (cost=112.00..6,251,768.10 rows=44,700 width=8) (actual rows= loops=)

  • Index Cond: (disbursement_date = '2018-10-03'::date)
24. 0.000 0.000 ↓ 0.0

Index Scan using gateway_transactions_pkey on gateway_transactions (cost=116.50..121.50 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = gateway_disputes.gateway_transaction_id)
25. 0.000 0.000 ↓ 0.0

CTE Scan on all_disputes (cost=0.00..2,759,698.50 rows=9,199 width=288) (actual rows= loops=)

  • Filter: (kind = 'chargeback'::text)
26. 0.000 0.000 ↓ 0.0

Hash (cost=94.00..94.00 rows=94 width=64) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

CTE Scan on braintree_chargeback_fees (cost=0.00..94.00 rows=94 width=64) (actual rows= loops=)