explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xGUu

Settings
# exclusive inclusive rows x rows loops node
1. 674.056 13,187.832 ↑ 1.0 1,001 1

HashAggregate (cost=2,694.39..2,706.91 rows=1,001 width=36) (actual time=13,181.316..13,187.832 rows=1,001 loops=1)

  • Group Key: tr.user_id
2.          

CTE _

3. 18.643 113.295 ↓ 1.0 1,461 1

Append (cost=140.34..228.86 rows=1,459 width=112) (actual time=22.593..113.295 rows=1,461 loops=1)

4.          

CTE last_rates

5. 10.762 20.785 ↑ 1.3 3 1

HashAggregate (cost=35.52..35.55 rows=4 width=16) (actual time=20.761..20.785 rows=3 loops=1)

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

Seq Scan on exchange_rates (cost=0.00..24.58 rows=1,458 width=16) (actual time=0.010..10.023 rows=1,458 loops=1)

7. 21.003 53.209 ↑ 1.0 1,457 1

WindowAgg (cost=104.78..137.56 rows=1,457 width=28) (actual time=22.572..53.209 rows=1,457 loops=1)

8. 21.044 32.206 ↑ 1.0 1,457 1

Sort (cost=104.78..108.42 rows=1,457 width=20) (actual time=22.468..32.206 rows=1,457 loops=1)

  • Sort Key: exchange_rates_1.from_currency, exchange_rates_1.ts
  • Sort Method: quicksort Memory: 162kB
9. 11.162 11.162 ↑ 1.0 1,457 1

Seq Scan on exchange_rates exchange_rates_1 (cost=0.00..28.23 rows=1,457 width=20) (actual time=0.019..11.162 rows=1,457 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
10. 0.030 0.050 ↑ 1.0 1 1

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

11. 0.020 0.020 ↑ 1.0 1 1

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

12. 10.353 41.393 ↓ 3.0 3 1

Hash Join (cost=0.15..41.14 rows=1 width=52) (actual time=21.152..41.393 rows=3 loops=1)

  • Hash Cond: (((er_1.from_currency)::text = (lr.from_currency)::text) AND ((er_1.to_currency)::text = (lr.to_currency)::text) AND (er_1.ts = lr.ts))
13. 10.155 10.155 ↑ 1.0 1,458 1

Seq Scan on exchange_rates er_1 (cost=0.00..24.58 rows=1,458 width=20) (actual time=0.026..10.155 rows=1,458 loops=1)

14. 0.045 20.885 ↑ 1.3 3 1

Hash (cost=0.08..0.08 rows=4 width=40) (actual time=20.880..20.885 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 20.840 20.840 ↑ 1.3 3 1

CTE Scan on last_rates lr (cost=0.00..0.08 rows=4 width=40) (actual time=20.777..20.840 rows=3 loops=1)

16. 11,814.476 12,513.776 ↑ 1.0 86,412 1

Hash Left Join (cost=32.91..1,817.44 rows=86,412 width=40) (actual time=144.608..12,513.776 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: 62947342
17. 554.920 554.920 ↑ 1.0 86,412 1

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

18. 10.314 144.380 ↓ 208.6 1,460 1

Hash (cost=32.83..32.83 rows=7 width=80) (actual time=144.374..144.380 rows=1,460 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
19. 134.066 134.066 ↓ 208.6 1,460 1

CTE Scan on _ er (cost=0.00..32.83 rows=7 width=80) (actual time=22.619..134.066 rows=1,460 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
Planning time : 0.581 ms
Execution time : 13,194.310 ms