I build systems to solve difficult problems. Expert in full-stack software development, hardware deployment, and networking computer systems in extreme environments.
Why would the database engine perform a full table scan on a limit/offset where the limit/offset factor is indexed? Can you show the query planner output for a query that actually exhibits this behaviour? Perhaps this behaviour is specific to the database engine you are using? I run massive limit/offset pagination queries in Postgres, where the limit/offset columns are indexed, and the performance is fantastic. The query planner is adamant that no table scans are occurring.
I build systems to solve difficult problems. Expert in full-stack software development, hardware deployment, and networking computer systems in extreme environments.
Follow up thought - Are you perhaps doing something funky like limiting/offsetting inside or after a common table expression? Maybe the query planner is dropping the ball at a CTE boundary? That's the most common way I end up blowing my leg off, performance wise... 😭😂
Why would the database engine perform a full table scan on a limit/offset where the limit/offset factor is indexed? Can you show the query planner output for a query that actually exhibits this behaviour? Perhaps this behaviour is specific to the database engine you are using? I run massive limit/offset pagination queries in Postgres, where the limit/offset columns are indexed, and the performance is fantastic. The query planner is adamant that no table scans are occurring.
Follow up thought - Are you perhaps doing something funky like limiting/offsetting inside or after a common table expression? Maybe the query planner is dropping the ball at a CTE boundary? That's the most common way I end up blowing my leg off, performance wise... 😭😂
Because most popular db engines doesn't a do full table scan, it already has optimizations for limit/offset.
This is a typical low quality article, not researched well enough and it was written for the sake of content...