explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ocNY : default + on true + limit

Settings

Optimization path:

Optimization(s) for this plan:

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

GroupAggregate (cost=1,586,481.66..1,587,358.29 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 ↑ 1.0 86,412 1

Sort (cost=1,586,373.24..1,586,589.27 rows=86,412 width=40) (actual rows=86,412 loops=1)

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

Nested Loop Left Join (cost=0.00..1,576,921.91 rows=86,412 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 ↑ 1.0 1 86,412

Limit (cost=0.00..18.21 rows=1 width=48) (actual rows=1 loops=86,412)

9. 0.000 0.000 ↑ 2.0 1 86,412

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

  • Filter: ((ts <= t.ts) AND ((t.currency)::text = (from_currency)::text))
  • Rows Removed by Filter: 712