Why does offset make your query slow?

When working with Pagination, whenever i receive an API request with a URL like: /employee?page=10&count=10, my sql query in the backend will handle this: "SELECT * FROM employee limit 10 offset 100". If the offset is small, my query will run very fast, but if the offset goes bigger and bigger my query will run very slowly.

Considering an example, i have a table employees with 2 columns: id and name. This table has 1 million records and has index in the id column.

Let's check the query plan when i have a limit and offset

The cost is very small and if i try to get offset 999900, let's see what happens

As you can see, i provide a limit 10 and an offset 999900, but the query plan shows me that it actually needs to get 999910 rows, and the cost is very high. So what does this mean? This means that even though you have a limit of 10, the database needs to fetch all 999910 rows and only get rows from 999900 to 999910 to show the result.

How to fix

The solution is that we can drop the offset and use ID instead. The ID column is the primary key and also has an index on it, so the cost will reduce and the database will only get 10 rows. Let's check it.

Voila, the cost is smaller than above, and the actual rows the database needs to fetch are only 10.

Use case

This approach is very helpful when your pagination has to type scroll to load more, lazy loading, or just the next or previous page because the front-end knows the biggest ID each time the next page is clicked. But you can't apply it to the pagination where the user can go to any page they want because the front-end can't track the ID.