在带有分区的clickhouse中选择最大(Primary_key)差性能( toYearWeek )

Question

下面的clickhouse表按年PARTITION BY toYearWeek(receive_time_utc)的周进行分区，并按主键排序，其中时间是主键元组的第一个元素：ORDER BY (receive_time_utc, seqno, rowno)

但是，SELECT max(receive_time_utc) FROM roq_bbo_okx似乎完成了对表的全面扫描，而不是只对最后一个分区进行扫描。

这可能是因为优化器不理解toYearWeek函数吗？我还让人们使用toYYYYMM函数和intDivs

CREATE TABLE default.roq_bbo_okx
(
    `gateway` LowCardinality(String),
    `session_id` UUID CODEC(LZ4),
    `seqno` UInt64 CODEC(DoubleDelta, LZ4),
    `rowno` UInt32 DEFAULT 0 CODEC(DoubleDelta, LZ4),
    `receive_time_utc` DateTime64(9, 'UTC') CODEC(DoubleDelta, LZ4),
    `exchange_time_utc` DateTime64(9, 'UTC') CODEC(DoubleDelta, LZ4),
    `origin_create_time_utc` DateTime64(9, 'UTC') DEFAULT receive_time_utc CODEC(DoubleDelta, LZ4),
    `snapshot` Int8 CODEC(LZ4),
    `exchange` LowCardinality(String) CODEC(LZ4),
    `symbol` LowCardinality(String) CODEC(LZ4),
    `type` Enum8('UNDEFINED' = 0, 'BID' = 48, 'ASK' = 49, 'TRADE' = 50, 'IMPLIED_BID' = 69, 'IMPLIED_ASK' = 70, 'BOOK_RESET' = 74) DEFAULT 'UNDEFINED' CODEC(LZ4),
    `action` Enum8('UNDEFINED' = 0, 'NEW' = 48, 'CHANGE' = 49, 'DELETE' = 50) DEFAULT 'UNDEFINED' CODEC(LZ4),
    `side` Enum8('UNDEFINED' = 0, 'BUY' = 48, 'SELL' = 49) DEFAULT 'UNDEFINED' CODEC(LZ4),
    `price_tick` Float64 DEFAULT toFloat64(nan) CODEC(Gorilla, LZ4),
    `price_i` Int64 DEFAULT -9223372036854775808 CODEC(DoubleDelta, LZ4),
    `qty_tick` Float64 DEFAULT toFloat64(nan) CODEC(Gorilla, LZ4),
    `qty_i` Int64 DEFAULT -9223372036854775808 CODEC(DoubleDelta, LZ4),
    `posno` UInt32 DEFAULT 0 CODEC(DoubleDelta, LZ4),
    `num_orders` UInt16 CODEC(Gorilla, LZ4),
    `trade_id` String CODEC(LZ4)
)
ENGINE = MergeTree
PARTITION BY toYearWeek(receive_time_utc)
PRIMARY KEY (receive_time_utc, seqno, rowno)
ORDER BY (receive_time_utc, seqno, rowno)
SETTINGS index_granularity = 8192 

Answer 1

目前还没有这样的优化。CH无法为max()使用主索引。在一些简单的情况下，CH能够使用基于partition.idx (分区列)的虚拟投影。

您可以手动优化order by ...desc limit 1

SELECT max(receive_time_utc) 
FROM roq_bbo_okx 
order by receive_time_utc desc limit 1