explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 56X0

Settings
# exclusive inclusive rows x rows loops node
1. 630.988 14,597.940 ↓ 35.8 1,001 1

GroupAggregate (cost=1,434.20..1,434.83 rows=28 width=36) (actual time=13,404.590..14,597.940 rows=1,001 loops=1)

  • Group Key: tr.user_id
2.          

CTE _

3. 18.279 135.079 ↓ 162.3 1,461 1

Append (cost=51.81..181.50 rows=9 width=112) (actual time=23.996..135.079 rows=1,461 loops=1)

4.          

CTE last_rates

5. 10.407 20.134 ↑ 66.7 3 1

HashAggregate (cost=35.52..37.52 rows=200 width=40) (actual time=20.110..20.134 rows=3 loops=1)

  • Group Key: exchange_rates.from_currency, exchange_rates.to_currency
6. 9.727 9.727 ↑ 1.0 1,458 1

Seq Scan on exchange_rates (cost=0.00..24.58 rows=1,458 width=40) (actual time=0.019..9.727 rows=1,458 loops=1)

7. 20.181 53.668 ↓ 208.1 1,457 1

WindowAgg (cost=14.30..14.45 rows=7 width=80) (actual time=23.980..53.668 rows=1,457 loops=1)

8. 21.387 33.487 ↓ 208.1 1,457 1

Sort (cost=14.30..14.31 rows=7 width=72) (actual time=23.958..33.487 rows=1,457 loops=1)

  • Sort Key: exchange_rates_1.from_currency, exchange_rates_1.ts
  • Sort Method: quicksort Memory: 162kB
9. 11.933 12.100 ↓ 208.1 1,457 1

Bitmap Heap Scan on exchange_rates exchange_rates_1 (cost=4.05..14.20 rows=7 width=72) (actual time=0.194..12.100 rows=1,457 loops=1)

  • Recheck Cond: ((to_currency)::text = 'GBP'::text)
  • Heap Blocks: exact=10
10. 0.167 0.167 ↓ 208.1 1,457 1

Bitmap Index Scan on to_curr_hash (cost=0.00..4.05 rows=7 width=0) (actual time=0.160..0.167 rows=1,457 loops=1)

  • Index Cond: ((to_currency)::text = 'GBP'::text)
11. 0.029 0.049 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=112) (actual time=0.024..0.049 rows=1 loops=1)

12. 0.020 0.020 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=84) (actual time=0.007..0.020 rows=1 loops=1)

13. 10.091 63.083 ↓ 3.0 3 1

Merge Join (cost=112.84..129.43 rows=1 width=80) (actual time=53.109..63.083 rows=3 loops=1)

  • Merge Cond: (((lr.from_currency)::text = (er_1.from_currency)::text) AND ((lr.to_currency)::text = (er_1.to_currency)::text) AND (lr.ts = er_1.ts))
14. 0.064 20.251 ↑ 66.7 3 1

Sort (cost=11.64..12.14 rows=200 width=40) (actual time=20.226..20.251 rows=3 loops=1)

  • Sort Key: lr.from_currency, lr.to_currency, lr.ts
  • Sort Method: quicksort Memory: 25kB
15. 20.187 20.187 ↑ 66.7 3 1

CTE Scan on last_rates lr (cost=0.00..4.00 rows=200 width=40) (actual time=20.125..20.187 rows=3 loops=1)

16. 22.154 32.741 ↑ 1.0 1,458 1

Sort (cost=101.20..104.84 rows=1,458 width=72) (actual time=23.353..32.741 rows=1,458 loops=1)

  • Sort Key: er_1.from_currency, er_1.to_currency, er_1.ts
  • Sort Method: quicksort Memory: 162kB
17. 10.587 10.587 ↑ 1.0 1,458 1

Seq Scan on exchange_rates er_1 (cost=0.00..24.58 rows=1,458 width=72) (actual time=0.015..10.587 rows=1,458 loops=1)

18. 1,203.612 13,966.952 ↓ 3,086.1 86,412 1

Sort (cost=1,252.70..1,252.77 rows=28 width=68) (actual time=13,404.058..13,966.952 rows=86,412 loops=1)

  • Sort Key: tr.user_id
  • Sort Method: external merge Disk: 1944kB
19. 12,028.561 12,763.340 ↓ 3,086.1 86,412 1

Hash Join (cost=0.21..1,252.03 rows=28 width=68) (actual time=165.427..12,763.340 rows=86,412 loops=1)

  • Hash Cond: ((tr.currency)::text = (er.from_currency)::text)
  • Join Filter: ((tr.ts >= er.start_ts) AND (tr.ts < er.end_ts))
  • Rows Removed by Join Filter: 62950505
20. 569.486 569.486 ↓ 1.7 86,412 1

Seq Scan on transactions tr (cost=0.00..1,057.92 rows=50,692 width=60) (actual time=0.022..569.486 rows=86,412 loops=1)

21. 9.994 165.293 ↓ 1,460.0 1,460 1

Hash (cost=0.20..0.20 rows=1 width=80) (actual time=165.287..165.293 rows=1,460 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
22. 155.299 155.299 ↓ 1,460.0 1,460 1

CTE Scan on _ er (cost=0.00..0.20 rows=1 width=80) (actual time=24.020..155.299 rows=1,460 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
Planning time : 0.326 ms
Execution time : 14,606.188 ms