explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5bMm

Settings
# exclusive inclusive rows x rows loops node
1. 642.074 8,120.372 ↑ 1.0 1,001 1

GroupAggregate (cost=3,479,699.34..3,480,575.97 rows=1,001 width=36) (actual time=6,915.597..8,120.372 rows=1,001 loops=1)

  • Group Key: tr.user_id
2.          

CTE _

3. 19.800 30.102 ↑ 1.0 1,459 1

Append (cost=0.00..39.18 rows=1,459 width=104) (actual time=0.029..30.102 rows=1,459 loops=1)

4. 10.252 10.252 ↑ 1.0 1,458 1

Seq Scan on exchange_rates (cost=0.00..24.58 rows=1,458 width=19) (actual time=0.017..10.252 rows=1,458 loops=1)

5. 0.036 0.050 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=104) (actual time=0.044..0.050 rows=1 loops=1)

6. 0.014 0.014 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=76) (actual time=0.008..0.014 rows=1 loops=1)

7. 1,191.241 7,478.298 ↑ 1.0 86,412 1

Sort (cost=3,479,660.16..3,479,876.19 rows=86,412 width=40) (actual time=6,915.180..7,478.298 rows=86,412 loops=1)

  • Sort Key: tr.user_id
  • Sort Method: external merge Disk: 2112kB
8. 2,182.123 6,287.057 ↑ 1.0 86,412 1

Nested Loop Left Join (cost=0.00..3,470,208.83 rows=86,412 width=40) (actual time=0.352..6,287.057 rows=86,412 loops=1)

9. 562.042 562.042 ↑ 1.0 86,412 1

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

10. 2,246.712 3,542.892 ↑ 1.0 1 86,412

Limit (cost=0.00..40.12 rows=1 width=96) (actual time=0.022..0.041 rows=1 loops=86,412)

11. 1,296.180 1,296.180 ↑ 1.0 1 86,412

CTE Scan on _ er (cost=0.00..40.12 rows=1 width=96) (actual time=0.009..0.015 rows=1 loops=86,412)

  • Filter: ((ts <= tr.ts) AND ((from_currency)::text = (tr.currency)::text) AND ((to_currency)::text = 'GBP'::text))
  • Rows Removed by Filter: 4
Planning time : 0.337 ms
Execution time : 8,127.775 ms