explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4dq8 : Optimization for: plan #vRDK

Settings

Optimization path:

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

GroupAggregate (cost=5,182.37..5,185.10 rows=103 width=36) (actual rows= loops=)

  • Group Key: totals.user_id
2.          

CTE totals

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,737.01..4,529.12 rows=28,804 width=124) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=3,737.01..3,809.02 rows=28,804 width=116) (actual rows= loops=)

  • Sort Key: t.user_id, t.ts, er.from_currency, er.to_currency, er.ts DESC
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=571.84..1,603.50 rows=28,804 width=116) (actual rows= loops=)

  • Hash Cond: ((t.currency)::text = (er.from_currency)::text)
  • Join Filter: (er.ts <= t.ts)
6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on transactions t (cost=543.52..1,454.57 rows=28,804 width=60) (actual rows= loops=)

  • Recheck Cond: (user_id < 30)
7. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_transactions_userid (cost=0.00..536.32 rows=28,804 width=0) (actual rows= loops=)

  • Index Cond: (user_id < 30)
8. 0.000 0.000 ↓ 0.0

Hash (cost=28.23..28.23 rows=7 width=72) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on exchange_rates er (cost=0.00..28.23 rows=7 width=72) (actual rows= loops=)

  • Filter: ((to_currency)::text = 'GBP'::text)
10. 0.000 0.000 ↓ 0.0

Sort (cost=653.25..653.61 rows=144 width=68) (actual rows= loops=)

  • Sort Key: totals.user_id
11. 0.000 0.000 ↓ 0.0

CTE Scan on totals (cost=0.00..648.09 rows=144 width=68) (actual rows= loops=)

  • Filter: (seq = 1)