explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4gTw : 123

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

GroupAggregate (cost=415,683.16..415,683.18 rows=1 width=36) (actual rows= loops=)

  • Group Key: t.user_id
2. 0.000 0.000 ↓ 0.0

Sort (cost=415,683.16..415,683.16 rows=1 width=11) (actual rows= loops=)

  • Sort Key: t.user_id
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,001.41..415,683.15 rows=1 width=11) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,001.14..415,682.84 rows=1 width=20) (actual rows= loops=)

  • Merge Cond: ((t.ts = transactions.ts) AND (t.user_id = transactions.user_id) AND ((t.currency)::text = (transactions.currency)::text))
5. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=1,000.72..409,606.30 rows=86,398 width=24) (actual rows= loops=)

  • Group Key: t.ts, t.user_id, t.currency
6. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,000.72..407,014.36 rows=172,796 width=24) (actual rows= loops=)

  • Workers Planned: 2
7. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=0.70..386,069.39 rows=86,398 width=24) (actual rows= loops=)

  • Group Key: t.ts, t.user_id, t.currency
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.70..297,783.28 rows=8,742,213 width=24) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using transactions_ts_user_id_currency_idx on transactions t (cost=0.42..3,412.40 rows=36,005 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Only Scan using exchange_rates_from_currency_to_currency_ts_idx on exchange_rates exr (cost=0.28..5.75 rows=243 width=12) (actual rows= loops=)

  • Index Cond: ((from_currency = (t.currency)::text) AND (to_currency = 'GBP'::text) AND (ts <= t.ts))
11. 0.000 0.000 ↓ 0.0

Index Scan using transactions_ts_user_id_currency_idx on transactions (cost=0.42..3,916.47 rows=86,412 width=20) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using exchange_rates_ts_idx on exchange_rates rates (cost=0.28..0.30 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (ts = (max(exr.ts)))
  • Filter: (((to_currency)::text = 'GBP'::text) AND ((from_currency)::text = (t.currency)::text))