explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nfow : my default

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=5,131,724.64..5,132,601.27 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=5,131,616.22..5,131,832.25 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=54.47..5,122,164.89 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

Subquery Scan on er (cost=54.47..59.27 rows=1 width=48) (actual rows=1 loops=86,412)

  • Filter: ((t.currency)::text = (er.from_currency)::text)
  • Rows Removed by Filter: 0
9. 0.000 0.000 ↑ 190.0 1 86,412

Unique (cost=54.47..56.90 rows=190 width=48) (actual rows=1 loops=86,412)

10. 0.000 0.000 ↑ 2.8 174 86,412

Sort (cost=54.47..55.68 rows=486 width=48) (actual rows=174 loops=86,412)

  • Sort Key: sorted_er.from_currency
  • Sort Method: quicksort Memory: 117kB
11. 0.000 0.000 ↓ 1.5 735 86,412

CTE Scan on sorted_er (cost=0.00..32.78 rows=486 width=48) (actual rows=735 loops=86,412)

  • Filter: (ts <= t.ts)
  • Rows Removed by Filter: 722
Planning time : 0.198 ms
Execution time : 13,991.011 ms