explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YpVS

Settings
# exclusive inclusive rows x rows loops node
1. 152.012 7,551.042 ↑ 1.0 1,001 1

GroupAggregate (cost=1,585,401.44..1,586,278.07 rows=1,001 width=36) (actual time=7,275.130..7,551.042 rows=1,001 loops=1)

  • Group Key: t.user_id
2.          

CTE er

3. 5.918 8.926 ↑ 1.0 1,456 1

Sort (cost=104.71..108.35 rows=1,456 width=16) (actual time=6.183..8.926 rows=1,456 loops=1)

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

Seq Scan on exchange_rates er_1 (cost=0.00..28.21 rows=1,456 width=16) (actual time=0.022..3.008 rows=1,456 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
5. 337.399 7,399.030 ↑ 1.0 86,412 1

Sort (cost=1,585,293.09..1,585,509.12 rows=86,412 width=40) (actual time=7,274.957..7,399.030 rows=86,412 loops=1)

  • Sort Key: t.user_id
  • Sort Method: external merge Disk: 1952kB
6. 541.367 7,061.631 ↑ 1.0 86,412 1

Nested Loop Left Join (cost=0.00..1,575,841.76 rows=86,412 width=40) (actual time=12.031..7,061.631 rows=86,412 loops=1)

7. 125.776 125.776 ↑ 1.0 86,412 1

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

8. 432.060 6,394.488 ↑ 1.0 1 86,412

Limit (cost=0.00..18.20 rows=1 width=32) (actual time=0.070..0.074 rows=1 loops=86,412)

9. 5,962.428 5,962.428 ↑ 2.0 1 86,412

CTE Scan on er (cost=0.00..36.40 rows=2 width=32) (actual time=0.067..0.069 rows=1 loops=86,412)

  • Filter: ((ts <= t.ts) AND ((from_currency)::text = (t.currency)::text))
  • Rows Removed by Filter: 723
Planning time : 0.379 ms
Execution time : 7,555.399 ms