Company or project name
No response
Describe the unexpected behaviour
Since a projection is a regular mergetree family table and go through the same MergeTree read-analysis path as base tables, if optimize_read_in_order is enabled and the query’s ORDER BY matches the projection’s sorting key prefix, ClickHouse should build InputOrderInfo via ReadInOrderOptimizer and read the projection “in order”. But it is not doing that although the analyzer/planner passes the same settings Context into projection analysis.
How to reproduce
Creating a new table with a simple projection ordered by timestamp
CREATE TABLE repro.optimize_read_in_order (
id UInt64,
timestamp DateTime,
value1 String,
value2 Float64,
value3 UInt64,
PROJECTION ts_projection
(
SELECT *
ORDER BY timestamp
)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (id, toStartOfHour(timestamp));
INSERT INTO repro.optimize_read_in_order
SELECT
id,
toDateTime('2025-01-01 00:00:00') + toIntervalSecond(rand() % 86400) AS timestamp,
value1,
value2,
value3
FROM generateRandom('id UInt64, value1 String, value2 Float64, value3 UInt64', 1, 20)
LIMIT 100000000;Explain actions for the first query with filtering and without order by
EXPLAIN actions = 1
SELECT *
FROM repro.optimize_read_in_order
WHERE (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2025-01-02 00:00:00')
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │
3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │
4. │ INPUT : 2 -> __table1.value1 String : 2 │
5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │
6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │
7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │
8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │
9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │
10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │
11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │
12. │ Positions: 5 0 1 2 3 │
13. │ Expression │
14. │ Actions: INPUT : 1 -> id UInt64 : 0 │
15. │ INPUT : 2 -> value1 String : 1 │
16. │ INPUT : 3 -> value2 Float64 : 2 │
17. │ INPUT : 4 -> value3 UInt64 : 3 │
18. │ INPUT : 0 -> timestamp DateTime : 4 │
19. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │
20. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │
21. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │
22. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │
23. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │
24. │ Positions: 5 3 0 1 2 │
25. │ ReadFromMergeTree (ts_projection) │
26. │ ReadType: Default │
27. │ Parts: 11 │
28. │ Granules: 12214 │
29. │ Prewhere info │
30. │ Need filter: 1 │
31. │ Prewhere filter │
32. │ Prewhere filter column: _projection_filter (removed) │
33. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │
34. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │
35. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │
36. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │
37. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │
38. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │
39. │ ALIAS and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) :: 2 -> _projection_filter UInt8 : 1 │
40. │ Positions: 0 1 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘This query is using the projection and streaming the data without external sort and expected results will be unordered, read type is default.
Second query: filtering + order by using projection
EXPLAIN actions = 1
SELECT *
FROM repro.optimize_read_in_order
WHERE (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2025-01-02 00:00:00')
ORDER BY timestamp ASC
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression (Project names) │
2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │
3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │
4. │ INPUT : 2 -> __table1.value1 String : 2 │
5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │
6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │
7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │
8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │
9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │
10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │
11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │
12. │ Positions: 5 0 1 2 3 │
13. │ Sorting (Sorting for ORDER BY) │
14. │ Sort description: __table1.timestamp ASC │
15. │ Expression │
16. │ Actions: INPUT : 1 -> id UInt64 : 0 │
17. │ INPUT : 2 -> value1 String : 1 │
18. │ INPUT : 3 -> value2 Float64 : 2 │
19. │ INPUT : 4 -> value3 UInt64 : 3 │
20. │ INPUT : 0 -> timestamp DateTime : 4 │
21. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │
22. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │
23. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │
24. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │
25. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │
26. │ Positions: 3 5 0 1 2 │
27. │ ReadFromMergeTree (ts_projection) │
28. │ ReadType: Default │
29. │ Parts: 11 │
30. │ Granules: 12214 │
31. │ Prewhere info │
32. │ Need filter: 1 │
33. │ Prewhere filter │
34. │ Prewhere filter column: _projection_filter (removed) │
35. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │
36. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │
37. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │
38. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │
39. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │
40. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │
41. │ ALIAS and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) :: 2 -> _projection_filter UInt8 : 1 │
42. │ Positions: 0 1 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘This one will read as the previous query and sort in memory although it is using the projection which is ordered, so expected behaviour here would be to use read in order.
Now let's create a table without projection but same structure and test the same queries to see the difference in the explain actions output.
CREATE TABLE optimize_read_in_order_no_prj
(
`id` UInt64,
`timestamp` DateTime,
`value1` String,
`value2` Float64,
`value3` UInt64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY timestamp
SETTINGS index_granularity = 8192
-- Used same inserts from aboveFirst query with filter and without order by
EXPLAIN actions = 1
SELECT *
FROM repro.max_bytes_ratio_before_external_sort_no_prj
WHERE (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2025-01-02 00:00:00')
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │
3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │
4. │ INPUT : 2 -> __table1.value1 String : 2 │
5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │
6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │
7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │
8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │
9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │
10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │
11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │
12. │ Positions: 5 0 1 2 3 │
13. │ Expression ((WHERE + Change column names to column identifiers)) │
14. │ Actions: INPUT : 0 -> id UInt64 : 0 │
15. │ INPUT : 2 -> value1 String : 1 │
16. │ INPUT : 3 -> value2 Float64 : 2 │
17. │ INPUT : 4 -> value3 UInt64 : 3 │
18. │ INPUT : 1 -> timestamp DateTime : 4 │
19. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │
20. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │
21. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │
22. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │
23. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │
24. │ Positions: 5 3 0 1 2 │
25. │ ReadFromMergeTree (repro.max_bytes_ratio_before_external_sort_no_prj) │
26. │ ReadType: Default │
27. │ Parts: 11 │
28. │ Granules: 12217 │
29. │ Prewhere info │
30. │ Need filter: 1 │
31. │ Prewhere filter │
32. │ Prewhere filter column: and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) (removed) │
33. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │
34. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │
35. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │
36. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │
37. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │
38. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │
39. │ Positions: 0 2 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘It is not using read in order just reading unordered in parallel and streaming as it goes, read method default, as the first query with projection.
Second query with filtering and with order by
EXPLAIN actions = 1
SELECT *
FROM repro.max_bytes_ratio_before_external_sort_no_prj
WHERE (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2025-01-02 00:00:00')
ORDER BY timestamp ASC
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression (Project names) │
2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │
3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │
4. │ INPUT : 2 -> __table1.value1 String : 2 │
5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │
6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │
7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │
8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │
9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │
10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │
11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │
12. │ Positions: 5 0 1 2 3 │
13. │ Sorting (Sorting for ORDER BY) │
14. │ Prefix sort description: __table1.timestamp ASC │
15. │ Result sort description: __table1.timestamp ASC │
16. │ Expression ((Before ORDER BY + Projection)) │
17. │ Actions: INPUT :: 0 -> __table1.id UInt64 : 0 │
18. │ INPUT :: 1 -> __table1.timestamp DateTime : 1 │
19. │ INPUT :: 2 -> __table1.value1 String : 2 │
20. │ INPUT :: 3 -> __table1.value2 Float64 : 3 │
21. │ INPUT :: 4 -> __table1.value3 UInt64 : 4 │
22. │ Positions: 1 0 2 3 4 │
23. │ Expression ((WHERE + Change column names to column identifiers)) │
24. │ Actions: INPUT : 0 -> id UInt64 : 0 │
25. │ INPUT : 2 -> value1 String : 1 │
26. │ INPUT : 3 -> value2 Float64 : 2 │
27. │ INPUT : 4 -> value3 UInt64 : 3 │
28. │ INPUT : 1 -> timestamp DateTime : 4 │
29. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │
30. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │
31. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │
32. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │
33. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │
34. │ Positions: 5 3 0 1 2 │
35. │ ReadFromMergeTree (repro.max_bytes_ratio_before_external_sort_no_prj) │
36. │ ReadType: InOrder │
37. │ Parts: 11 │
38. │ Granules: 12217 │
39. │ Prewhere info │
40. │ Need filter: 1 │
41. │ Prewhere filter │
42. │ Prewhere filter column: and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) (removed) │
43. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │
44. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │
45. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │
46. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │
47. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │
48. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │
49. │ Positions: 0 2 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘It will use read in order now (read type readInOrder) because of ORDER BY which is expected behaviour.
Expected behavior
Projections should levearage the optimize_read_in_order setting, as regular mergetree tables
Error message and/or stacktrace
No response
Additional context
No response
Company or project name
No response
Describe the unexpected behaviour
Since a projection is a regular mergetree family table and go through the same MergeTree read-analysis path as base tables, if optimize_read_in_order is enabled and the query’s ORDER BY matches the projection’s sorting key prefix, ClickHouse should build InputOrderInfo via ReadInOrderOptimizer and read the projection “in order”. But it is not doing that although the analyzer/planner passes the same settings Context into projection analysis.
How to reproduce
Creating a new table with a simple projection ordered by timestamp
Explain actions for the first query with filtering and without order by
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ Expression ((Project names + Projection)) │ 2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │ 3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │ 4. │ INPUT : 2 -> __table1.value1 String : 2 │ 5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │ 6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │ 7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │ 8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │ 9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │ 10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │ 11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │ 12. │ Positions: 5 0 1 2 3 │ 13. │ Expression │ 14. │ Actions: INPUT : 1 -> id UInt64 : 0 │ 15. │ INPUT : 2 -> value1 String : 1 │ 16. │ INPUT : 3 -> value2 Float64 : 2 │ 17. │ INPUT : 4 -> value3 UInt64 : 3 │ 18. │ INPUT : 0 -> timestamp DateTime : 4 │ 19. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │ 20. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │ 21. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │ 22. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │ 23. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │ 24. │ Positions: 5 3 0 1 2 │ 25. │ ReadFromMergeTree (ts_projection) │ 26. │ ReadType: Default │ 27. │ Parts: 11 │ 28. │ Granules: 12214 │ 29. │ Prewhere info │ 30. │ Need filter: 1 │ 31. │ Prewhere filter │ 32. │ Prewhere filter column: _projection_filter (removed) │ 33. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │ 34. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │ 35. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │ 36. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │ 37. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │ 38. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │ 39. │ ALIAS and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) :: 2 -> _projection_filter UInt8 : 1 │ 40. │ Positions: 0 1 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘This query is using the projection and streaming the data without external sort and expected results will be unordered, read type is default.
Second query: filtering + order by using projection
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ Expression (Project names) │ 2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │ 3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │ 4. │ INPUT : 2 -> __table1.value1 String : 2 │ 5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │ 6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │ 7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │ 8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │ 9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │ 10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │ 11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │ 12. │ Positions: 5 0 1 2 3 │ 13. │ Sorting (Sorting for ORDER BY) │ 14. │ Sort description: __table1.timestamp ASC │ 15. │ Expression │ 16. │ Actions: INPUT : 1 -> id UInt64 : 0 │ 17. │ INPUT : 2 -> value1 String : 1 │ 18. │ INPUT : 3 -> value2 Float64 : 2 │ 19. │ INPUT : 4 -> value3 UInt64 : 3 │ 20. │ INPUT : 0 -> timestamp DateTime : 4 │ 21. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │ 22. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │ 23. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │ 24. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │ 25. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │ 26. │ Positions: 3 5 0 1 2 │ 27. │ ReadFromMergeTree (ts_projection) │ 28. │ ReadType: Default │ 29. │ Parts: 11 │ 30. │ Granules: 12214 │ 31. │ Prewhere info │ 32. │ Need filter: 1 │ 33. │ Prewhere filter │ 34. │ Prewhere filter column: _projection_filter (removed) │ 35. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │ 36. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │ 37. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │ 38. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │ 39. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │ 40. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │ 41. │ ALIAS and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) :: 2 -> _projection_filter UInt8 : 1 │ 42. │ Positions: 0 1 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘This one will read as the previous query and sort in memory although it is using the projection which is ordered, so expected behaviour here would be to use read in order.
Now let's create a table without projection but same structure and test the same queries to see the difference in the explain actions output.
First query with filter and without order by
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ Expression ((Project names + Projection)) │ 2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │ 3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │ 4. │ INPUT : 2 -> __table1.value1 String : 2 │ 5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │ 6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │ 7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │ 8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │ 9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │ 10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │ 11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │ 12. │ Positions: 5 0 1 2 3 │ 13. │ Expression ((WHERE + Change column names to column identifiers)) │ 14. │ Actions: INPUT : 0 -> id UInt64 : 0 │ 15. │ INPUT : 2 -> value1 String : 1 │ 16. │ INPUT : 3 -> value2 Float64 : 2 │ 17. │ INPUT : 4 -> value3 UInt64 : 3 │ 18. │ INPUT : 1 -> timestamp DateTime : 4 │ 19. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │ 20. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │ 21. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │ 22. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │ 23. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │ 24. │ Positions: 5 3 0 1 2 │ 25. │ ReadFromMergeTree (repro.max_bytes_ratio_before_external_sort_no_prj) │ 26. │ ReadType: Default │ 27. │ Parts: 11 │ 28. │ Granules: 12217 │ 29. │ Prewhere info │ 30. │ Need filter: 1 │ 31. │ Prewhere filter │ 32. │ Prewhere filter column: and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) (removed) │ 33. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │ 34. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │ 35. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │ 36. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │ 37. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │ 38. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │ 39. │ Positions: 0 2 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘It is not using read in order just reading unordered in parallel and streaming as it goes, read method default, as the first query with projection.
Second query with filtering and with order by
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ Expression (Project names) │ 2. │ Actions: INPUT : 0 -> __table1.id UInt64 : 0 │ 3. │ INPUT : 1 -> __table1.timestamp DateTime : 1 │ 4. │ INPUT : 2 -> __table1.value1 String : 2 │ 5. │ INPUT : 3 -> __table1.value2 Float64 : 3 │ 6. │ INPUT : 4 -> __table1.value3 UInt64 : 4 │ 7. │ ALIAS __table1.id :: 0 -> id UInt64 : 5 │ 8. │ ALIAS __table1.timestamp :: 1 -> timestamp DateTime : 0 │ 9. │ ALIAS __table1.value1 :: 2 -> value1 String : 1 │ 10. │ ALIAS __table1.value2 :: 3 -> value2 Float64 : 2 │ 11. │ ALIAS __table1.value3 :: 4 -> value3 UInt64 : 3 │ 12. │ Positions: 5 0 1 2 3 │ 13. │ Sorting (Sorting for ORDER BY) │ 14. │ Prefix sort description: __table1.timestamp ASC │ 15. │ Result sort description: __table1.timestamp ASC │ 16. │ Expression ((Before ORDER BY + Projection)) │ 17. │ Actions: INPUT :: 0 -> __table1.id UInt64 : 0 │ 18. │ INPUT :: 1 -> __table1.timestamp DateTime : 1 │ 19. │ INPUT :: 2 -> __table1.value1 String : 2 │ 20. │ INPUT :: 3 -> __table1.value2 Float64 : 3 │ 21. │ INPUT :: 4 -> __table1.value3 UInt64 : 4 │ 22. │ Positions: 1 0 2 3 4 │ 23. │ Expression ((WHERE + Change column names to column identifiers)) │ 24. │ Actions: INPUT : 0 -> id UInt64 : 0 │ 25. │ INPUT : 2 -> value1 String : 1 │ 26. │ INPUT : 3 -> value2 Float64 : 2 │ 27. │ INPUT : 4 -> value3 UInt64 : 3 │ 28. │ INPUT : 1 -> timestamp DateTime : 4 │ 29. │ ALIAS id :: 0 -> __table1.id UInt64 : 5 │ 30. │ ALIAS value1 :: 1 -> __table1.value1 String : 0 │ 31. │ ALIAS value2 :: 2 -> __table1.value2 Float64 : 1 │ 32. │ ALIAS value3 :: 3 -> __table1.value3 UInt64 : 2 │ 33. │ ALIAS timestamp :: 4 -> __table1.timestamp DateTime : 3 │ 34. │ Positions: 5 3 0 1 2 │ 35. │ ReadFromMergeTree (repro.max_bytes_ratio_before_external_sort_no_prj) │ 36. │ ReadType: InOrder │ 37. │ Parts: 11 │ 38. │ Granules: 12217 │ 39. │ Prewhere info │ 40. │ Need filter: 1 │ 41. │ Prewhere filter │ 42. │ Prewhere filter column: and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String)) (removed) │ 43. │ Actions: INPUT : 0 -> timestamp DateTime : 0 │ 44. │ COLUMN Const(String) -> '2025-01-01 00:00:00'_String String : 1 │ 45. │ COLUMN Const(String) -> '2025-01-02 00:00:00'_String String : 2 │ 46. │ FUNCTION greaterOrEquals(timestamp : 0, '2025-01-01 00:00:00'_String :: 1) -> greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) UInt8 : 3 │ 47. │ FUNCTION lessOrEquals(timestamp : 0, '2025-01-02 00:00:00'_String :: 2) -> lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) UInt8 : 1 │ 48. │ FUNCTION and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String) :: 3, lessOrEquals(__table1.timestamp, '2025-01-02 00:00:00'_String) :: 1) -> and(greaterOrEquals(__table1.timestamp, '2025-01-01 00:00:00'_String), lessOrEquals(__table1.timesta... UInt8 : 2 │ 49. │ Positions: 0 2 │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘It will use read in order now (read type readInOrder) because of ORDER BY which is expected behaviour.
Expected behavior
Projections should levearage the optimize_read_in_order setting, as regular mergetree tables
Error message and/or stacktrace
No response
Additional context
No response