explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xNJ

Settings
# exclusive inclusive rows x rows loops node
1. 5.664 5.664 ↑ 1,275.8 288 1

CTE Scan on ohlcv (cost=131,412.07..138,760.95 rows=367,444 width=48) (actual time=5.445..5.664 rows=288 loops=1)

2.          

CTE intervals

3. 0.389 0.389 ↓ 2.4 2,353 1

Function Scan on generate_series start (cost=0.00..12.50 rows=1,000 width=16) (actual time=0.192..0.389 rows=2,353 loops=1)

4.          

CTE ohlcv

5. 0.120 5.595 ↑ 1,275.8 288 1

Unique (cost=124,969.30..131,399.57 rows=367,444 width=56) (actual time=5.443..5.595 rows=288 loops=1)

6. 0.273 5.475 ↑ 435.4 844 1

Sort (cost=124,969.30..125,887.91 rows=367,444 width=56) (actual time=5.442..5.475 rows=844 loops=1)

  • Sort Key: intervals.start, (first_value(ohlcv_10min.open) OVER (?)), (max(ohlcv_10min.high) OVER (?)), (min(ohlcv_10min.low) OVER (?)), (last_value(ohlcv_10min.close) OVER (?)), (sum(ohlcv_10min.volume) OVER (?))
  • Sort Method: quicksort Memory: 143kB
7. 0.705 5.202 ↑ 435.4 844 1

WindowAgg (cost=67,419.49..78,442.81 rows=367,444 width=56) (actual time=4.478..5.202 rows=844 loops=1)

8. 0.245 4.497 ↑ 435.4 844 1

Sort (cost=67,419.49..68,338.10 rows=367,444 width=52) (actual time=4.463..4.497 rows=844 loops=1)

  • Sort Key: intervals.start, ohlcv_10min.date_time
  • Sort Method: quicksort Memory: 143kB
9. 1.092 4.252 ↑ 435.4 844 1

Nested Loop (cost=0.42..20,893.00 rows=367,444 width=52) (actual time=0.694..4.252 rows=844 loops=1)

10. 0.807 0.807 ↓ 2.4 2,353 1

CTE Scan on intervals (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.193..0.807 rows=2,353 loops=1)

11. 2.353 2.353 ↓ 0.0 0 2,353

Index Scan using ohlcv_10min_pkey on ohlcv_10min (cost=0.42..17.20 rows=367 width=44) (actual time=0.001..0.001 rows=0 loops=2,353)

  • Index Cond: ((security_id = 490,855) AND (date_time >= intervals.start) AND (date_time < intervals."end"))
Planning time : 0.420 ms
Execution time : 5.910 ms