Why dose Clickhouse performs slow when order by tuple?

Question

Table define

I use clickhouse to store logs, logs are imported into clickhouse as rows.
I have a table tableA with PRIMARY KEY id ORDER BY (id, capture_time, offset), where capture_time is DateTime64(3) and offset is UInt64.

    CREATE TABLE dbA.tableA ON CLUSTER clusterA (
        `id` String,
        `offset` UInt64,
        `capture_time` DateTime64(3),
        `server_time` DateTime64(3),
        `indexed_tags` Map(String, String),
        `unindexed_tags` Map(String, String),
        `content` String
    ) ENGINE = HaMergeTree(
     '/clickhouse/clickhouse_common/dbA.tableA/{shard}',
        '{replica}'
    ) PARTITION BY toDate(capture_time) 
    PRIMARY KEY psm
    ORDER BY
        (id, capture_time, offset) 
    TTL toDate(capture_time) + INTERVAL 30 DAY 
    SETTINGS index_granularity = 4096

Query case

My query case is SELECT columnA from tableA where id = 'id_a' order by (capture_time, offset) desc limit 1000.
But I found that query using order by (capture_time, offset)(takes 6 sec) is much slower than order by capture_time or order by offset(takes 1 sec), which confused me very much.

Does anyone familiar with clickhouse tell me why this is? Or how can I speed up my query? Thanks in advance!

where id = ? order by (capture_time,offset):

1000 rows in set. Elapsed: 8.347 sec. Match materialized view: 0. Processed 1.21 million rows, 3.48 GB uncompressed bytes, 3.42 GB compressed bytes (144.81 thousand rows/s, 416.90 MB/s, 409.47 MB/s.)

where id = ? order by capture_time

1000 rows in set. Elapsed: 0.900 sec. Match materialized view: 0. Processed 1.21 million rows, 3.46 GB uncompressed bytes, 3.41 GB compressed bytes (1.34 million rows/s, 3.84 GB/s, 3.79 GB/s.)

where id = ? order by offset

1000 rows in set. Elapsed: 0.845 sec. Match materialized view: 0. Processed 1.21 million rows, 3.46 GB uncompressed bytes, 3.42 GB compressed bytes (1.43 million rows/s, 4.10 GB/s, 4.05 GB/s.)

order by (capture_time, offset) without where filter

1000 rows in set. Elapsed: 11.025 sec. Match materialized view: 0. Processed 1.65 million rows, 4.73 GB uncompressed bytes, 4.68 GB compressed bytes (150.06 thousand rows/s, 428.86 MB/s, 424.30 MB/s.)

Answer 1

It should be without ( )

SELECT columnA from tableA 
where id = 'id_a' 
order by capture_time, offset 
desc limit 1000