explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g6wD

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 100.456 193,388.746 ↑ 1.0 1,001 1

GroupAggregate (cost=4,819,672.69..4,820,549.32 rows=1,001 width=36) (actual time=193,206.562..193,388.746 rows=1,001 loops=1)

  • Group Key: t.user_id
2. 296.725 193,288.290 ↑ 1.0 86,412 1

Sort (cost=4,819,672.69..4,819,888.72 rows=86,412 width=12) (actual time=193,206.458..193,288.290 rows=86,412 loops=1)

  • Sort Key: t.user_id
  • Sort Method: external merge Disk: 1952kB
3. 287.617 192,991.565 ↑ 1.0 86,412 1

Nested Loop Left Join (cost=53.49..4,812,587.36 rows=86,412 width=12) (actual time=0.203..192,991.565 rows=86,412 loops=1)

4. 91.600 91.600 ↑ 1.0 86,412 1

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

5. 259.236 192,612.348 ↑ 1.0 1 86,412

Subquery Scan on er (cost=53.49..55.94 rows=1 width=8) (actual time=2.229..2.229 rows=1 loops=86,412)

  • Filter: ((t.currency)::text = (er.from_currency)::text)
  • Rows Removed by Filter: 1
6. 15,467.748 192,353.112 ↑ 1.0 2 86,412

Unique (cost=53.49..55.92 rows=2 width=16) (actual time=1.877..2.226 rows=2 loops=86,412)

7. 109,656.828 176,885.364 ↑ 2.5 191 86,412

Sort (cost=53.49..54.70 rows=485 width=16) (actual time=1.874..2.047 rows=191 loops=86,412)

  • Sort Key: exchange_rates.from_currency, exchange_rates.ts DESC
  • Sort Method: quicksort Memory: 139kB
8. 67,228.536 67,228.536 ↓ 1.5 734 86,412

Seq Scan on exchange_rates (cost=0.00..31.86 rows=485 width=16) (actual time=0.004..0.778 rows=734 loops=86,412)

  • Filter: ((ts <= t.ts) AND ((to_currency)::text = 'GBP'::text))
  • Rows Removed by Filter: 723
Planning time : 0.181 ms
Execution time : 193,391.757 ms