explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jwXa

Settings
# exclusive inclusive rows x rows loops node
1. 649.622 14,501.457 ↓ 35.8 1,001 1

GroupAggregate (cost=1,378.12..1,378.75 rows=28 width=36) (actual time=13,275.933..14,501.457 rows=1,001 loops=1)

  • Group Key: tr.user_id
2.          

CTE _

3. 18.581 132.876 ↓ 243.5 1,461 1

Append (cost=46.34..125.49 rows=6 width=112) (actual time=23.356..132.876 rows=1,461 loops=1)

4.          

CTE last_rates

5. 10.154 19.544 ↑ 66.7 3 1

HashAggregate (cost=24.18..26.18 rows=200 width=40) (actual time=19.518..19.544 rows=3 loops=1)

  • Group Key: exchange_rates.from_currency, exchange_rates.to_currency
6. 9.390 9.390 ↓ 1.8 1,458 1

Seq Scan on exchange_rates (cost=0.00..18.10 rows=810 width=40) (actual time=0.021..9.390 rows=1,458 loops=1)

7. 21.198 54.474 ↓ 364.2 1,457 1

WindowAgg (cost=20.16..20.26 rows=4 width=80) (actual time=23.342..54.474 rows=1,457 loops=1)

8. 21.586 33.276 ↓ 364.2 1,457 1

Sort (cost=20.16..20.18 rows=4 width=72) (actual time=23.321..33.276 rows=1,457 loops=1)

  • Sort Key: exchange_rates_1.from_currency, exchange_rates_1.ts
  • Sort Method: quicksort Memory: 162kB
9. 11.690 11.690 ↓ 364.2 1,457 1

Seq Scan on exchange_rates exchange_rates_1 (cost=0.00..20.12 rows=4 width=72) (actual time=0.020..11.690 rows=1,457 loops=1)

  • Filter: ((to_currency)::text = 'GBP'::text)
  • Rows Removed by Filter: 1
10. 0.031 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.019 0.019 ↑ 1.0 1 1

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

12. 9.758 59.771 ↓ 3.0 3 1

Merge Join (cost=68.87..78.98 rows=1 width=80) (actual time=49.986..59.771 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))
13. 0.066 19.664 ↑ 66.7 3 1

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

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

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

15. 20.814 30.349 ↓ 1.8 1,458 1

Sort (cost=57.23..59.26 rows=810 width=72) (actual time=21.188..30.349 rows=1,458 loops=1)

  • Sort Key: er_1.from_currency, er_1.to_currency, er_1.ts
  • Sort Method: quicksort Memory: 162kB
16. 9.535 9.535 ↓ 1.8 1,458 1

Seq Scan on exchange_rates er_1 (cost=0.00..18.10 rows=810 width=72) (actual time=0.016..9.535 rows=1,458 loops=1)

17. 1,216.797 13,851.835 ↓ 3,086.1 86,412 1

Sort (cost=1,252.63..1,252.70 rows=28 width=68) (actual time=13,275.507..13,851.835 rows=86,412 loops=1)

  • Sort Key: tr.user_id
  • Sort Method: external merge Disk: 1944kB
18. 11,908.788 12,635.038 ↓ 3,086.1 86,412 1

Hash Join (cost=0.15..1,251.96 rows=28 width=68) (actual time=163.652..12,635.038 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: 62964370
19. 562.718 562.718 ↓ 1.7 86,412 1

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

20. 10.201 163.532 ↓ 1,460.0 1,460 1

Hash (cost=0.14..0.14 rows=1 width=80) (actual time=163.526..163.532 rows=1,460 loops=1)

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

CTE Scan on _ er (cost=0.00..0.14 rows=1 width=80) (actual time=23.376..153.331 rows=1,460 loops=1)

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