felixge / felixge/flame-explain
Something unreadable on window functions and with
- Dominant language
- TypeScript
- Stars
- 55
- Forks
- 5
- PR merge metrics
- No merged PRs in 30d
Description
Here is the request
```
EXPLAIN (ANALYZE, FORMAT JSON, VERBOSE, BUFFERS)
with ordered_candles as (
select *,
RANK () OVER (
PARTITION BY symbol, length
ORDER BY
open_time desc
) local_order
from candles
order by symbol, open_time desc
)
select * from ordered_candles where
(length = interval '1 second' and local_order <= 3)
or (length = interval '1 minute' and local_order <= 5)
order by symbol,length, open_time;
```
Here is the result
```
[
{
"Plan": {
"Node Type": "Sort",
"Parallel Aware": false,
"Startup Cost": 797437.97,
"Total Cost": 799127.51,
"Plan Rows": 675818,
"Plan Width": 72,
"Actual Startup Time": 13603.668,
"Actual Total Time": 13603.680,
"Actual Rows": 184,
"Actual Loops": 1,
"Output": ["ordered_candles.symbol", "ordered_candles.open_time", "ordered_candles.close_time", "ordered_candles.opening", "ordered_candles.closing", "ordered_candles.high", "ordered_candles.low", "ordered_candles.length", "ordered_candles.local_order"],
"Sort Key": ["ordered_candles.symbol", "ordered_candles.length", "ordered_candles.open_time"],
"Sort Method": "quicksort",
"Sort Space Used": 50,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 42884,
"Temp Written Blocks": 42894,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "ordered_candles",
"Startup Cost": 685839.23,
"Total Cost": 731997.58,
"Plan Rows": 675818,
"Plan Width": 72,
"Actual Startup Time": 12389.337,
"Actual Total Time": 13603.442,
"Actual Rows": 184,
"Actual Loops": 1,
"Output": ["ordered_candles.symbol", "ordered_candles.open_time", "ordered_candles.close_time", "ordered_candles.opening", "ordered_candles.closing", "ordered_candles.high", "ordered_candles.low", "ordered_candles.length", "ordered_candles.local_order"],
"Filter": "(((ordered_candles.length = '00:00:01'::interval) AND (ordered_candles.local_order <= 3)) OR ((ordered_candles.length = '00:01:00'::interval) AND (ordered_candles.local_order <= 5)))",
"Rows Removed by Filter": 2051522,
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 42884,
"Temp Written Blocks": 42894,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 685839.23,
"Total Cost": 690967.94,
"Plan Rows": 2051482,
"Plan Width": 72,
"Actual Startup Time": 12389.331,
"Actual Total Time": 13245.752,
"Actual Rows": 2051706,
"Actual Loops": 1,
"Output": ["candles.symbol", "candles.open_time", "candles.close_time", "candles.opening", "candles.closing", "candles.high", "candles.low", "candles.length", "(rank() OVER (?))"],
"Sort Key": ["candles.symbol", "candles.open_time DESC"],
"Sort Method": "external merge",
"Sort Space Used": 183536,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 42884,
"Temp Written Blocks": 42894,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 340457.60,
"Total Cost": 386615.95,
"Plan Rows": 2051482,
"Plan Width": 72,
"Actual Startup Time": 6914.242,
"Actual Total Time": 9030.894,
"Actual Rows": 2051706,
"Actual Loops": 1,
"Output": ["candles.symbol", "candles.open_time", "candles.close_time", "candles.opening", "candles.closing", "candles.high", "candles.low", "candles.length", "rank() OVER (?)"],
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 19942,
"Temp Written Blocks": 19947,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 340457.60,
"Total Cost": 345586.31,
"Plan Rows": 2051482,
"Plan Width": 64,
"Actual Startup Time": 6914.229,
"Actual Total Time": 7618.606,
"Actual Rows": 2051706,
"Actual Loops": 1,
"Output": ["candles.symbol", "candles.open_time", "candles.length", "candles.close_time", "candles.opening", "candles.closing", "candles.high", "candles.low"],
"Sort Key": ["candles.symbol", "candles.length", "candles.open_time DESC"],
"Sort Method": "external merge",
"Sort Space Used": 159536,
"Sort Space Type": "Disk",
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 19942,
"Temp Written Blocks": 19947,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "candles",
"Schema": "public",
"Alias": "candles",
"Startup Cost": 0.00,
"Total Cost": 48244.82,
"Plan Rows": 2051482,
"Plan Width": 64,
"Actual Startup Time": 0.010,
"Actual Total Time": 395.029,
"Actual Rows": 2051706,
"Actual Loops": 1,
"Output": ["candles.symbol", "candles.open_time", "candles.length", "candles.close_time", "candles.opening", "candles.closing", "candles.high", "candles.low"],
"Shared Hit Blocks": 27730,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 0.127,
"Triggers": [
],
"Execution Time": 13623.108
}
]
```
Flame graph is unreadable
Contributor guide
Assessment
This issue has not been assessed yet.