explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6WYg

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.097 28,066.803 ↓ 5.0 1,001 1

GroupAggregate (cost=1,234,355.19..1,234,864.61 rows=200 width=40) (actual time=28,032.327..28,066.803 rows=1,001 loops=1)

  • Group Key: tr.user_id
2. 86.650 28,040.706 ↓ 1.7 86,412 1

Sort (cost=1,234,355.19..1,234,481.92 rows=50,692 width=68) (actual time=28,032.288..28,040.706 rows=86,412 loops=1)

  • Sort Key: tr.user_id
  • Sort Method: external merge Disk: 1936kB
3. 32.757 27,954.056 ↓ 1.7 86,412 1

Nested Loop Left Join (cost=24.19..1,228,311.24 rows=50,692 width=68) (actual time=0.223..27,954.056 rows=86,412 loops=1)

4. 10.223 10.223 ↓ 1.7 86,412 1

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

5. 86.412 27,911.076 ↑ 1.0 1 86,412

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

6. 3,629.304 27,824.664 ↑ 1.0 1 86,412

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

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

Seq Scan on exchange_rates er (cost=0.00..24.18 rows=1 width=40) (actual time=0.003..0.280 rows=367 loops=86,412)

  • Filter: ((ts <= tr.ts) AND ((to_currency)::text = 'GBP'::text) AND ((from_currency)::text = (tr.currency)::text))
  • Rows Removed by Filter: 1091
Planning time : 0.167 ms
Execution time : 28,068.036 ms