Unexpected scan when using projections + order by on replacingmergetree

[luca-nardelli]

Describe the unexpected behaviour
Clickhouse version: Clickhouse cloud (version 22.13)

We have the following test table setup:

CREATE TABLE test_table (
    `timestamp` DateTime64(3),
    `user_id` LowCardinality(String),
    PROJECTION by_user_id
    (
        SELECT *
        ORDER BY 
            `user_id`,
            `timestamp`
    )
)
ENGINE = ReplicatedReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY timestamp
ORDER BY (timestamp, user_id)
SETTINGS index_granularity = 1024;

We have created the additional projection to support filtering the events by user_id.

We populate the table with sample data

INSERT INTO test_table(timestamp, user_id)
SELECT 
	now() - randUniform(1, 5000000.)
	, 'user_a' AS user_id
FROM numbers(1000000)	
	;
	
INSERT INTO test_table(timestamp, user_id)
SELECT 
	now() - randUniform(1, 5000000.)
	, 'user_b' AS user_id
FROM numbers(1000000)	
	;

SELECT user_id, COUNT(), MIN(timestamp) AS timestamp_min, MAX(timestamp) AS timestamp_max  FROM test_table GROUP BY user_id;

┌─user_id─┬─count()─┬───────────timestamp_min─┬───────────timestamp_max─┐
│ user_b  │  906779 │ 2023-01-09 14:47:57.000 │ 2023-03-08 11:41:15.000 │
│ user_a  │  906420 │ 2023-01-09 14:47:56.000 │ 2023-03-08 11:41:11.000 │
└─────────┴─────────┴─────────────────────────┴─────────────────────────┘

Everything is fine if we run

SELECT * FROM test_table ORDER BY timestamp DESC LIMIT 10;

10 rows in set. Elapsed: 0.003 sec. Processed 2.77 thousand rows, 25.02 KB (1.10 million rows/s., 9.91 MB/s.)

But things get weird if we start writing queries that use the projection.

1. Much higher number of rows read with WHERE clause

SELECT * FROM test_table WHERE user_id='user_a' LIMIT 10;

10 rows in set. Elapsed: 0.009 sec. Processed 182.27 thousand rows, 1.64 MB (21.16 million rows/s., 190.44 MB/s.)

Shouldn't this still read ~2k rows since the projection is ordered by user_id, timestamp ?

2. "Full scan" when using WHERE clause with ORDER BY

SELECT * FROM test_table WHERE user_id='user_a' ORDER BY timestamp DESC LIMIT 10;

10 rows in set. Elapsed: 0.025 sec. Processed 906.77 thousand rows, 8.16 MB (36.33 million rows/s., 326.97 MB/s.)

Same as above, this should read only the first ~2k rows since it's exactly along the projection ORDER BY, instead it's reading all of the entries for user user_a.

Additional context

EXPLAINs for the above queries

EXPLAIN plan actions=1,indexes=1 SELECT * FROM test_table WHERE user_id='user_a' LIMIT 10;
┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                        │
│ Actions: INPUT :: 0 -> user_id LowCardinality(String) : 0          │
│          INPUT :: 1 -> timestamp DateTime64(3) : 1                 │
│ Positions: 1 0                                                     │
│   Limit (preliminary LIMIT (without OFFSET))                       │
│   Limit 10                                                         │
│   Offset 0                                                         │
│     ReadFromStorage (MergeTree(with Normal projection by_user_id)) │
└────────────────────────────────────────────────────────────────────┘

EXPLAIN plan actions=1,indexes=1 SELECT * FROM test_table WHERE user_id='user_a' ORDER BY timestamp DESC LIMIT 10;
┌─explain────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                │
│ Actions: INPUT :: 0 -> user_id LowCardinality(String) : 0              │
│          INPUT :: 1 -> timestamp DateTime64(3) : 1                     │
│ Positions: 1 0                                                         │
│   Limit (preliminary LIMIT (without OFFSET))                           │
│   Limit 10                                                             │
│   Offset 0                                                             │
│     Sorting (Sorting for ORDER BY)                                     │
│     Sort description: timestamp DESC                                   │
│     Limit 10                                                           │
│       Expression (Before ORDER BY)                                     │
│       Actions: INPUT :: 0 -> user_id LowCardinality(String) : 0        │
│                INPUT :: 1 -> timestamp DateTime64(3) : 1               │
│       Positions: 0 1                                                   │
│         ReadFromStorage (MergeTree(with Normal projection by_user_id)) │
└────────────────────────────────────────────────────────────────────────┘

We are currently using materialized views to achieve the same goal but I was trying out if projection could be a fit for our use case (as it looked much more ergonomic in terms of maintenance and consistency), after going through https://clickhouse.com/blog/clickhouse-faster-queries-with-projections-and-primary-indexes

Could be linked to:

• Projections inconsistent results for ReplacingMergeTree/CollapsingMergeTree #24778
• Projections are not used in ReplacingMergeTree #46968

[den-crane]

multiple threads

SELECT * FROM test_table WHERE user_id='user_a' LIMIT 10 settings max_threads=64;
 Processed 357.38 thousand rows,

SELECT * FROM test_table WHERE user_id='user_a' LIMIT 10 settings max_threads=1;
Processed 123.90 thousand rows

SELECT * FROM test_table WHERE user_id='user_a' ORDER BY timestamp DESC LIMIT 10;

Projections are not used for ORDER BY timestamp. There is no such feature.

[luca-nardelli]

Projections are not used for ORDER BY timestamp. There is no such feature.

Technically, the EXPLAIN I added at the end of my post shows that Clickhouse is indeed reading from the projection, but instead of reading in order like regular tables (which, if the projection is like a hidden table, it should be able to do) it reads all the rows for user_a.

Apart from this, is there a way to achieve what I want (i.e. find the most recent events for a given user) using projections then or do I have to resort to Materialized views?

https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-multiple/#option-3-projections

If projections cannot be used for these use cases, I suppose che docs for clickhouse cloud should probably be updated to reflect this difference, because by reading this

Because effectively the hidden table (and it's primary index) created by the projection is identical to the secondary table that we created explicitly, the query is executed in the same effective way as with the explicitly created table.

My assumption is that projections are functionally equivalent to materialized views, while instead they're not.

[den-crane]

Technically, the EXPLAIN I added at the end of my post shows that Clickhouse is indeed reading from the projection, but instead of reading in order like regular tables (which, if the projection is like a hidden table, it should be able to do) it reads all the rows for user_a.

It's expected. By design. No such feature.

Apart from this, is there a way to achieve what I want (i.e. find the most recent events for a given user) using projections then or do I have to resort to Materialized views?

You have to resort to Materialized views.

If projections cannot be used for these use cases, I suppose che docs for clickhouse cloud should probably be updated to reflect this difference, because by reading this

Yes, they should. Please make a pull request.

[luca-nardelli]

Thanks @den-crane for the quick answer, as usual 😄 . I opened a PR for the docs

[den-crane]

see also #33678