explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P4ei : Optimization for: Optimization for: plan #6WYg; plan #HwfV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.333 10,796.881 ↓ 5.0 1,001 1

Sort (cost=423,839.34..423,839.84 rows=200 width=40) (actual time=10,796.825..10,796.881 rows=1,001 loops=1)

  • Sort Key: tr.user_id
  • Sort Method: quicksort Memory: 71kB
2. 76.566 10,796.548 ↓ 5.0 1,001 1

HashAggregate (cost=423,829.20..423,831.70 rows=200 width=40) (actual time=10,796.349..10,796.548 rows=1,001 loops=1)

  • Group Key: tr.user_id
3. 80.162 10,719.982 ↓ 1.7 86,412 1

Nested Loop Left Join (cost=8.31..423,449.01 rows=50,692 width=68) (actual time=0.035..10,719.982 rows=86,412 loops=1)

4. 11.144 11.144 ↓ 1.7 86,412 1

Seq Scan on transactions tr (cost=0.00..1,057.92 rows=50,692 width=60) (actual time=0.010..11.144 rows=86,412 loops=1)

5. 0.000 10,628.676 ↑ 1.0 1 86,412

Limit (cost=8.31..8.31 rows=1 width=40) (actual time=0.123..0.123 rows=1 loops=86,412)

6. 3,629.304 10,628.676 ↑ 1.0 1 86,412

Sort (cost=8.31..8.31 rows=1 width=40) (actual time=0.123..0.123 rows=1 loops=86,412)

  • Sort Key: er.ts DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
7. 6,999.372 6,999.372 ↓ 367.0 367 86,412

Index Scan using idx_ts_from_to on exchange_rates er (cost=0.28..8.30 rows=1 width=40) (actual time=0.011..0.081 rows=367 loops=86,412)

  • Index Cond: (((from_currency)::text = (tr.currency)::text) AND (ts <= tr.ts))
Planning time : 0.152 ms
Execution time : 10,796.972 ms