explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sSua

Settings
# exclusive inclusive rows x rows loops node
1. 121.226 4,601.269 ↑ 1.0 1,001 1

HashAggregate (cost=9,952.65..9,965.16 rows=1,001 width=36) (actual time=4,600.166..4,601.269 rows=1,001 loops=1)

  • Group Key: t.user_id
2.          

CTE er

3. 3.508 5.294 ↑ 1.0 1,455 1

Sort (cost=104.71..108.35 rows=1,456 width=16) (actual time=3.668..5.294 rows=1,455 loops=1)

  • Sort Key: er_2.ts DESC
  • Sort Method: quicksort Memory: 140kB
4. 1.786 1.786 ↑ 1.0 1,456 1

Seq Scan on exchange_rates er_2 (cost=0.00..28.21 rows=1,456 width=16) (actual time=0.014..1.786 rows=1,456 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
5. 250.407 4,480.043 ↑ 1.0 86,412 1

Nested Loop Left Join (cost=0.00..9,196.21 rows=86,412 width=40) (actual time=7.403..4,480.043 rows=86,412 loops=1)

6. 81.860 81.860 ↑ 1.0 86,412 1

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

7. 345.648 4,147.776 ↑ 1.0 1 86,412

Subquery Scan on er (cost=0.00..0.08 rows=1 width=48) (actual time=0.045..0.048 rows=1 loops=86,412)

  • Filter: ((er.from_currency)::text = (t.currency)::text)
  • Rows Removed by Filter: 0
8. 259.236 3,802.128 ↑ 1.0 1 86,412

Limit (cost=0.00..0.07 rows=1 width=48) (actual time=0.042..0.044 rows=1 loops=86,412)

9. 3,542.892 3,542.892 ↑ 485.0 1 86,412

CTE Scan on er er_1 (cost=0.00..32.76 rows=485 width=48) (actual time=0.040..0.041 rows=1 loops=86,412)

  • Filter: (ts <= t.ts)
  • Rows Removed by Filter: 722
Planning time : 0.297 ms
Execution time : 4,602.240 ms