Why adding OFFSET to the clickhouse query increase execution time?

Question

I have a table with approximately 9 million records. When I'm trying to select records with big offset(for pagination) it increase execution time to extremely values. Or even causing an exceeding of memory limits and fails.

Here are logs for query with two different offset values.

SELECT * WHERE set_date >= '2019-10-11 11:05:00' AND set_date <= '2019-10-19 18:09:59' ORDER BY id ASC LIMIT 1 OFFSET 30

Elapsed: 0.729 sec. Processed 9.92 million rows, 3.06 GB (13.61 million rows/s., 4.19 GB/s.) 
MemoryTracker: Peak memory usage (for query): 181.65 MiB.

SELECT * WHERE set_date >= '2019-10-11 11:05:00' AND set_date <= '2019-10-19 18:09:59' ORDER BY id ASC LIMIT 1 OFFSET 3000000

Elapsed: 6.301 sec. Processed 9.92 million rows, 3.06 GB (1.57 million rows/s., 485.35 MB/s.) 
MemoryTracker: Peak memory usage (for query): 5.89 GiB.

Can you try to use the Between command with the Id? SELECT * (missing the from table here) WHERE set_date BETWEEN ''2019-10-11 11:05:00' AND '2019-10-19 18:09:59' and id between 1 and 3000000 ORDER BY id ASC — Pimenta
Could you provide the table schema? I suspect used PK like ORDER BY (set_date, id), if it is right try to select .. order by set_date, id limit 1 offset 3000000. — vladimir
Why do you ever SELECT * from any table? Use the column names you care about to conserve resources required to complete the query. What was the elapsed time using BETWEEN start-date AND end-date (as suggested by Pimenta) ? But then you would need an index on the date that might not be available. — Wilson Hauck

Denny Crane Denny Crane · Accepted Answer · 2020-03-12T17:31:41

All databases including CH implement OFFSET the same way. They just read all rows and skip OFFSET in a resultset. There is no optimization to ascend right into OFFSET 3000000.

https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/

try to disable optimize_read_in_order to fix memory usage

SELECT * 
WHERE set_date >= '2019-10-11 11:05:00' 
AND set_date <= '2019-10-19 18:09:59' 
ORDER BY id ASC LIMIT 1 OFFSET 3000000
setting optimize_read_in_order=0

Why adding OFFSET to the clickhouse query increase execution time?

1 Answers