explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g1Du

Settings
# exclusive inclusive rows x rows loops node
1. 49,203.833 49,203.833 ↑ 13.2 695 1

Hash Left Join (cost=70,801,189.16..73,569,941.41 rows=9,199 width=320) (actual time=48,214.730..49,203.833 rows=695 loops=1)

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

CTE all_disputes

3. 10.343 1,791.756 ↑ 2,530.7 727 1

Nested Loop (cost=445,667.81..30,962,497.37 rows=1,839,794 width=398) (actual time=804.498..1,791.756 rows=727 loops=1)

  • -> 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 time=0.016..0.038 rows=3 loo
4. 0.746 1,781.413 ↑ 20.9 208 1

Nested Loop Left Join (cost=445,551.31..12,690,025.78 rows=4,337 width=94) (actual time=804.361..1,781.413 rows=208 loops=1)

  • -> 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 time=0.003..0.003 rows=1 loops=20
  • Index Cond: (gateway_transaction_id = gd.gateway_transaction_id)
  • Heap Fetches: 773
5. 0.517 1,780.667 ↑ 20.9 208 1

Hash Left Join (cost=445,439.81..12,189,451.62 rows=4,337 width=102) (actual time=804.332..1,780.667 rows=208 loops=1)

  • Hash Cond: (gd.processing_record_id = aib_chargebacks.id)
  • Join Filter: (gd.processing_record_type = 'AIB::Chargeback'::text)
  • Index Cond: ((convertable_id = gd.id) AND (convertable_type = 'Gateway::Dispute'::text))
  • Heap Fetches: 0
6. 7.928 1,732.244 ↑ 20.9 208 1

Hash Join (cost=353,926.81..12,094,658.87 rows=4,337 width=122) (actual time=756.151..1,732.244 rows=208 loops=1)

  • Hash Cond: (gt.merchant_account_id = ma.id)
7. 603.897 1,372.008 ↓ 1.0 45,183 1

Nested Loop (cost=228.50..11,705,267.06 rows=44,700 width=115) (actual time=402.806..1,372.008 rows=45,183 loops=1)

  • -> Index Scan using index_gateway_disputes_on_kind_and_disbursement_date on gateway_disputes gd (cost=112.00..6251769.56 rows=44700 width=84) (actual time=402.736..5
  • Index Cond: (disbursement_date = '2018-10-03'::date)
8. 768.111 768.111 ↑ 1.0 1 45,183

Index Scan using gateway_transactions_pkey on gateway_transactions gt (cost=116.50..121.50 rows=1 width=31) (actual time=0.016..0.017 rows=1 loops=45,183)

  • Index Cond: (id = gd.gateway_transaction_id)
9. 352.308 352.308 ↑ 1.1 130,342 1

Hash (cost=216,576.31..216,576.31 rows=137,122 width=23) (actual time=352.308..352.308 rows=130,342 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9177kB
  • -> Index Scan using index_merchant_accounts_on_region on merchant_accounts ma (cost=85.50..216576.31 rows=137122 width=23) (actual time=0.041..314.121 rows=130342 lo
  • Index Cond: (region = 'EUR'::bt_merchant_accounts_region_enum)
10. 8.810 47.906 ↑ 1.0 58,371 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3695kB
11. 39.096 39.096 ↑ 1.0 58,371 1

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

12.          

CTE braintree_chargeback_fees

13. 31.071 47,384.947 ↓ 480.3 45,146 1

GroupAggregate (cost=39,838,268.79..39,838,503.79 rows=94 width=40) (actual time=47,349.860..47,384.947 rows=45,146 loops=1)

  • Group Key: gateway_transactions.processor_id
14. 377.830 47,353.876 ↓ 536.9 50,465 1

Sort (cost=39,838,268.79..39,838,315.79 rows=94 width=16) (actual time=47,349.844..47,353.876 rows=50,465 loops=1)

  • Sort Key: gateway_transactions.processor_id
  • Sort Method: quicksort Memory: 5479kB
15. 46,976.046 46,976.046 ↓ 536.9 50,465 1

Hash Join (cost=11,750,052.60..39,837,652.66 rows=94 width=16) (actual time=20,307.367..46,976.046 rows=50,465 loops=1)

  • Hash Cond: (gateway_transaction_fees.gateway_transaction_id = gateway_transactions.id)
  • -> Index Scan using index_gw_transaction_fees_on_gw_dispute_id_and_kind on gateway_transaction_fees (cost=87.00..20373929.05 rows=10284948 width=16) (actual time=0.050..43371.73
  • Filter: ((promotional_credit_amount = 0) AND (kind = 'chargeback'::text))