[SOLVED] Postgres offset will scan the whole table if I add where clause to the query?

Postgres offset will scan the whole table if I add where clause to the query?

I have let's say 100 000 records where 20 records relates to a concrete user. I am looking for confirmation that adding where clause to the query will make offset clause to scan only these 20 rows. e.g.

record is a table and it contains the index for user_id

SELECT r.user_id, r.book_name
FROM record r
WHERE r.user_id = 1
ORDER BY r.book_name
LIMIT 2
OFFSET 2;

Offset in this query should scan only 20 rows for this concreate user, is it right?
Can I go on to use offset clause given I use where with a concrete user id if the records table can contain a billion of records ? Will it be still "fast"?

Thank you.

Solution

The LIMIT and the OFFSET is applied to the data from the ORDER BY. The ORDER BY is applied to the data after the JOINs, WHERE clause, SELECT functions, group bys, etc. The data needs to be ordered to find the offset and then the limit. The query is asking for a window into the ordered data.

Any where clause predicate that reduces the data will allow the ORDER BY to work faster since there are fewer tuples to order.

Please look at the EXPLAIN to see the plan.

PostgreSQL has optimization to perform a different sort (top-n heapsort) in many of these cases where there is a LIMIT/OFFSET. You can search many articles on optimizing OFFSET in stackexchange. Here is blog post.

The quotes with "fast" is in the eye of the beholder. Fast enough for the use case. There might need to be other tuning, such as work_mem, and indexes to get the query to be "fast enough".