clickhouse - ClickHouse ，选择 top n 并按主键排序的问题

Question

当我像这样查询时，我有一个包含 30 亿行的表

Select * from tsnew where time > 971128806382 and time <971172006000
limit 100

它工作正常，需要 0.2 秒但是当向查询添加订单时，如下所示：

Select * from tsnew where time > 971128806382 and time <971172006000
order by time desc
limit 100

这需要很长时间（超过 20 秒）。

create table tsnew(
ext_rec_num Nullable(UInt64),
xdr_id Nullable(UInt64),
xdr_grp Nullable(UInt64),
xdr_type Nullable(UInt64),
xdr_subtype Nullable(Int16),
xdr_direction Nullable(Int16),
xdr_location Nullable(Int16),
time UInt64,
stop_time UInt64,
transaction_duration Nullable(UInt64),
response_time Nullable(UInt64),
protocol Nullable(Int16),
chunk_count Nullable(Int16),
dpc Nullable(Int32), 
opc Nullable(Int32), 
first_link_id String,
last_dpc Nullable(Int32),
last_opc Nullable(Int32),
last_link_id String,
first_back_opc Nullable(Int32),
first_back_link_id String,
calling_ssn Nullable(Int16),
called_ssn Nullable(Int16),
called_sccp_address String,
calling_party_address String,
response_calling_address String,
root_end_code Nullable(Int32),
root_cause_code Nullable(Int32),
root_cause_pl Nullable(Int16),
root_failure Nullable(Int16),
root_equip Nullable(Int16)
)
ENGINE = MergeTree()
PARTITION BY toInt64(time/3600000)*3600000 
order by time
SETTINGS index_granularity = 8192

谁可以帮我这个事？

标签： clickhouse

Answer 1

这是已知问题。希望尽快合并。订阅 PR 并在 PR 合并时升级您的 CH。