CODEWITHSUNDEEP
clickhouse
Nastia Leaptinc
Nastia Leaptinc

Reputation: 1

Why adding OFFSET to the clickhouse query increase execution time?

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.

Upvotes: 0

Views: 2852

Answers (1)

Denny Crane
Denny Crane

Reputation: 13310

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

Upvotes: 1

Related Questions