For instance, consider the following two queries:
SELECT id, CHARACTER_LENGTH(polygon_data)
FROM polygon_data
LIMIT 10 OFFSET 1;
SELECT id, CHARACTER_LENGTH(polygon_data)
FROM polygon_data
WHERE id = 9;
I noticed that the second query (by specific id) runs much faster than the first one with OFFSET, even though the OFFSET query isn’t fetching the skipped rows—it’s just returning the 11th row onward.
My expectation was that the query with OFFSET would be faster because it doesn’t return the skipped rows, but the database seems to slow down significantly as the offset increases.
Why does using OFFSET cause such a performance hit, even when the skipped rows aren’t fetched?
How can I optimize queries that require pagination with large offsets?
5
There are many factors that the query performance is depending on, first one is the index so in your case maybe the index of the table is the id column so the second query will be much faster as you don’t need to scan all the table and if the id column is not the index, also the second one will be the faster as the first query will need to count the first 10 rows, then it will take the 11th and the higher offset the higher time it will take.
You may do this to improve the first query As I am assuming the id is the index column
SELECT
id, CHARACTER_LENGTH(polygon_data)
FROM
polygon_data
WHERE
id > 10
LIMIT 1;
Mohamed Fathi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.