zachjsh commented on PR #15415: URL: https://github.com/apache/druid/pull/15415#issuecomment-1848427855
> For performance-reasons, I think it'd be best if we don't use `OFFSET` on the segments table. Using `OFFSET` to page through results in a cluster with large number of segments will become a bottleneck. An alternative idea is to leverage the indices `id`, `start` and `end` built for the segments table along with the uniqueness of segment `id` via the primary key constraint, we can guarantee a stable sort and page through batched results from a client. > > Instead of an `OFFSET` based paginated query: > > ```sql > SELECT "start", "end", "id" > FROM druid_segments > WHERE dataSource = 'foo' AND "start" >= '2023-01-01' AND "end" < '2024-01-01' > ORDER BY 1, 2 > LIMIT 100 > OFFSET 10000 > ``` > > We can do a cursor-based query: > > ```sql > SELECT "start", "end", "id" > FROM druid_segments > WHERE dataSource = 'foo' AND "start" >= '2023-01-01' AND "end" < '2024-01-01' AND id > 'foo_2023-02-24T07:00:00.000Z_2023-02-24T08:00:00.000Z_2023-11-20T23:04:26.856Z' > ORDER BY 1, 2, 3 > LIMIT 100 > ``` > > The execution plans for these two queries are both `simple` from `EXPLAIN PLAN`, but the first query has to read, count and then discard results because of `OFFSET`, so it'll take longer especially for larger values of `OFFSET`. With the second query, an API caller should pass in the last segment id from a previous batch (if any); they can also narrow down the search interval as they batch through results as another optimization. Good suggestion, updated. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
