explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lvy5 : default + on true

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↑ 1.0 1,001 1

GroupAggregate (cost=3,172,300.40..3,174,041.15 rows=1,001 width=36) (actual rows=1,001 loops=1)

  • Group Key: t.user_id
2.          

CTE sorted_er

3. 0.000 0.000 ↑ 1.0 1,457 1

Sort (cost=104.78..108.42 rows=1,457 width=20) (actual rows=1,457 loops=1)

  • Sort Key: exchange_rates.from_currency, exchange_rates.ts DESC
  • Sort Method: quicksort Memory: 162kB
4. 0.000 0.000 ↑ 1.0 1,457 1

Seq Scan on exchange_rates (cost=0.00..28.23 rows=1,457 width=20) (actual rows=1,457 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
5. 0.000 0.000 ↑ 2.0 86,412 1

Sort (cost=3,172,191.98..3,172,624.04 rows=172,824 width=40) (actual rows=86,412 loops=1)

  • Sort Key: t.user_id
  • Sort Method: external merge Disk: 1960kB
6. 0.000 0.000 ↑ 2.0 86,412 1

Nested Loop Left Join (cost=0.00..3,152,428.70 rows=172,824 width=40) (actual rows=86,412 loops=1)

7. 0.000 0.000 ↑ 1.0 86,412 1

Seq Scan on transactions t (cost=0.00..1,415.12 rows=86,412 width=20) (actual rows=86,412 loops=1)

8. 0.000 0.000 ↑ 2.0 1 86,412

Unique (cost=0.00..36.43 rows=2 width=48) (actual rows=1 loops=86,412)

9. 0.000 0.000 ↓ 183.5 367 86,412

CTE Scan on sorted_er (cost=0.00..36.43 rows=2 width=48) (actual rows=367 loops=86,412)

  • Filter: ((ts <= t.ts) AND ((t.currency)::text = (from_currency)::text))
  • Rows Removed by Filter: 1090
Planning time : 0.273 ms
Execution time : 8,967.116 ms