explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0EZ6

Settings
# exclusive inclusive rows x rows loops node
1. 389.298 2,749.325 ↑ 1.0 293,155 1

HashAggregate (cost=511,404.69..518,105.06 rows=297,794 width=180) (actual time=2,618.707..2,749.325 rows=293,155 loops=1)

  • Group Key: offline_payments.offline_paymentid, offline_payments.orderid, replace(split_part((offline_payments.transactionid)::text, '_'::text, 2), 'op-'::text, ''::text), offline_payments.status, d.statusid, offline_payments.creation_date, b.creation_date, d.creation_date, d.status_date, b.due_date, b.file_date, b.payment_method, b.bank, b.provider, offline_payments.payment_method, offline_payments.intermediary, offline_payments.processor, CASE WHEN (date_trunc('month'::text, b.creation_date) <> date_trunc('month'::text, d.status_date)) THEN 1 ELSE 0 END, offline_payments.amount, b.transaction_amount, d.movement_amount, b.fee_amount
2. 207.556 2,360.027 ↑ 1.0 293,155 1

Hash Left Join (cost=246,130.85..495,026.02 rows=297,794 width=180) (actual time=450.563..2,360.027 rows=293,155 loops=1)

  • Hash Cond: (offline_payments.offline_paymentid = b.offline_paymentid)
3. 1,117.595 2,111.854 ↑ 1.0 293,155 1

Hash Right Join (cost=242,723.72..486,733.92 rows=297,794 width=96) (actual time=409.348..2,111.854 rows=293,155 loops=1)

  • Hash Cond: (d.orderid = offline_payments.orderid)
4. 587.064 587.064 ↑ 1.0 5,253,506 1

Seq Scan on wallet_transactions_aggr d (cost=0.00..221,738.02 rows=5,253,702 width=29) (actual time=0.006..587.064 rows=5,253,506 loops=1)

5. 109.107 407.195 ↑ 1.0 293,155 1

Hash (cost=239,001.30..239,001.30 rows=297,794 width=71) (actual time=407.195..407.195 rows=293,155 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 36,452kB
6. 298.088 298.088 ↑ 1.0 293,155 1

Index Scan using i_offline_payments_status_date on offline_payments (cost=0.43..239,001.30 rows=297,794 width=71) (actual time=0.026..298.088 rows=293,155 loops=1)

  • Index Cond: ((status_date >= '2020-05-01 00:00:00'::timestamp without time zone) AND (status_date <= '2020-05-31 00:00:00'::timestamp without time zone))
7. 24.103 40.617 ↓ 1.0 93,664 1

Hash (cost=2,236.50..2,236.50 rows=93,650 width=63) (actual time=40.617..40.617 rows=93,664 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 10,013kB
8. 16.514 16.514 ↓ 1.0 93,664 1

Seq Scan on external_cashin_files b (cost=0.00..2,236.50 rows=93,650 width=63) (actual time=0.006..16.514 rows=93,664 loops=1)

Planning time : 0.841 ms
Execution time : 2,772.822 ms