explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gvYbf : Optimization for: plan #g6wD

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 101.270 159,263.723 ↑ 1.0 1,001 1

GroupAggregate (cost=5,124,871.74..5,125,748.37 rows=1,001 width=36) (actual time=159,080.342..159,263.723 rows=1,001 loops=1)

  • Group Key: t.user_id
2.          

CTE sorted_transactions

3. 3.302 4.901 ↑ 1.0 1,456 1

Sort (cost=104.71..108.35 rows=1,456 width=20) (actual time=3.583..4.901 rows=1,456 loops=1)

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

Seq Scan on exchange_rates (cost=0.00..28.21 rows=1,456 width=20) (actual time=0.028..1.599 rows=1,456 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
5. 288.184 159,162.453 ↑ 1.0 86,412 1

Sort (cost=5,124,763.39..5,124,979.42 rows=86,412 width=40) (actual time=159,080.233..159,162.453 rows=86,412 loops=1)

  • Sort Key: t.user_id
  • Sort Method: external merge Disk: 1952kB
6. 294.962 158,874.269 ↑ 1.0 86,412 1

Nested Loop Left Join (cost=54.40..5,115,312.07 rows=86,412 width=40) (actual time=6.432..158,874.269 rows=86,412 loops=1)

7. 99.699 99.699 ↑ 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..99.699 rows=86,412 loops=1)

8. 259.236 158,479.608 ↑ 1.0 1 86,412

Subquery Scan on er (cost=54.40..59.20 rows=1 width=48) (actual time=1.834..1.834 rows=1 loops=86,412)

  • Filter: ((t.currency)::text = (er.from_currency)::text)
  • Rows Removed by Filter: 1
9. 15,467.748 158,220.372 ↑ 95.0 2 86,412

Unique (cost=54.40..56.82 rows=190 width=48) (actual time=1.481..1.831 rows=2 loops=86,412)

10. 76,647.444 142,752.624 ↑ 2.5 191 86,412

Sort (cost=54.40..55.61 rows=485 width=48) (actual time=1.479..1.652 rows=191 loops=86,412)

  • Sort Key: sorted_transactions.from_currency
  • Sort Method: quicksort Memory: 117kB
11. 66,105.180 66,105.180 ↓ 1.5 734 86,412

CTE Scan on sorted_transactions (cost=0.00..32.76 rows=485 width=48) (actual time=0.020..0.765 rows=734 loops=86,412)

  • Filter: (ts <= t.ts)
  • Rows Removed by Filter: 722
Planning time : 0.212 ms
Execution time : 159,266.819 ms