Check out our latest eBook: "Efficient Search in Rails with Postgres". We've updated our Terms of Service and Privacy Policy, effective Jan 30, 2021.

EXPLAIN - Insights: Large Offset

Description:

When a query uses a LIMIT, it fetches data from the underlying table until the desired number of rows is reached. This is pretty efficient (if you need the top N rows according to some specific ordering, the data may need to be sorted first and that can be expensive, but the LIMIT itself is cheap).

However, when adding an OFFSET, there is no easy way to skip past those rows: they must be read from disk and discarded. A query with LIMIT 10 OFFSET 1000 will need to read 1010 rows from disk, and throw away 1000 of them. As the offset grows larger and larger, this becomes more and more inefficient.

Recommended Action:

If using offsets for pagination, consider a more efficient pagination strategy, e.g. keyset-based pagination.

Learn More:



Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →