#	exclusive	inclusive	rows x	rows	loops	node
1.	26.097	28,066.803	↓ 5.0	1,001	1	→ GroupAggregate (cost=1,234,355.19..1,234,864.61 rows=200 width=40) (actual time=28,032.327..28,066.803 rows=1,001 loops=1) Group Key: tr.user_id
2.	86.650	28,040.706	↓ 1.7	86,412	1	→ Sort (cost=1,234,355.19..1,234,481.92 rows=50,692 width=68) (actual time=28,032.288..28,040.706 rows=86,412 loops=1) Sort Key: tr.user_id Sort Method: external merge Disk: 1936kB
3.	32.757	27,954.056	↓ 1.7	86,412	1	→ Nested Loop Left Join (cost=24.19..1,228,311.24 rows=50,692 width=68) (actual time=0.223..27,954.056 rows=86,412 loops=1)
4.	10.223	10.223	↓ 1.7	86,412	1	→ Seq Scan on transactions tr (cost=0.00..1,057.92 rows=50,692 width=60) (actual time=0.012..10.223 rows=86,412 loops=1)
5.	86.412	27,911.076	↑ 1.0	1	86,412	→ Limit (cost=24.19..24.19 rows=1 width=40) (actual time=0.323..0.323 rows=1 loops=86,412)
6.	3,629.304	27,824.664	↑ 1.0	1	86,412	→ Sort (cost=24.19..24.19 rows=1 width=40) (actual time=0.322..0.322 rows=1 loops=86,412) Sort Key: er.ts DESC NULLS LAST Sort Method: top-N heapsort Memory: 25kB
7.	24,195.360	24,195.360	↓ 367.0	367	86,412	→ Seq Scan on exchange_rates er (cost=0.00..24.18 rows=1 width=40) (actual time=0.003..0.280 rows=367 loops=86,412) Filter: ((ts <= tr.ts) AND ((to_currency)::text = 'GBP'::text) AND ((from_currency)::text = (tr.currency)::text)) Rows Removed by Filter: 1091

Planning time	:	0.167 ms
Execution time	:	28,068.036 ms

GroupAggregate  (cost=1234355.19..1234864.61 rows=200 width=40) (actual time=28032.327..28066.803 rows=1001 loops=1)
  Group Key: tr.user_id
  ->  Sort  (cost=1234355.19..1234481.92 rows=50692 width=68) (actual time=28032.288..28040.706 rows=86412 loops=1)
        Sort Key: tr.user_id
        Sort Method: external merge  Disk: 1936kB
        ->  Nested Loop Left Join  (cost=24.19..1228311.24 rows=50692 width=68) (actual time=0.223..27954.056 rows=86412 loops=1)
              ->  Seq Scan on transactions tr  (cost=0.00..1057.92 rows=50692 width=60) (actual time=0.012..10.223 rows=86412 loops=1)
              ->  Limit  (cost=24.19..24.19 rows=1 width=40) (actual time=0.323..0.323 rows=1 loops=86412)
                    ->  Sort  (cost=24.19..24.19 rows=1 width=40) (actual time=0.322..0.322 rows=1 loops=86412)
                          Sort Key: er.ts DESC NULLS LAST
                          Sort Method: top-N heapsort  Memory: 25kB
                          ->  Seq Scan on exchange_rates er  (cost=0.00..24.18 rows=1 width=40) (actual time=0.003..0.280 rows=367 loops=86412)
                                Filter: ((ts <= tr.ts) AND ((to_currency)::text = 'GBP'::text) AND ((from_currency)::text = (tr.currency)::text))
                                Rows Removed by Filter: 1091
Planning time: 0.167 ms
Execution time: 28068.036 ms

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	26.097 ms	0.1 %
Limit	1	86.412 ms	0.3 %
Nested Loop Left Join	1	32.757 ms	0.1 %
Seq Scan	2	24,205.583 ms	86.2 %
Sort	2	3,715.954 ms	13.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
exchange_rates	1	24,195.360 ms	86.2 %
Seq Scan	1	24,195.360 ms	100.0 %
transactions	1	10.223 ms	0.0 %
Seq Scan	1	10.223 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6WYg

Color mode:

Visible columns:

Optimization(s) for this plan:

Per node type stats

Per table stats