PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    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".

    Login or Signup to reply.
  2. 0

    When you execute a query with a WHERE clause followed by an OFFSET and LIMIT clause, the database first filters the records based on the WHERE clause. After filtering, it applies the OFFSET and LIMIT constraints.

    In your query, the OFFSET clause operates only on the rows returned by the WHERE clause, meaning it skips the first n rows of the filtered results and then applies the LIMIT to return the specified number of rows. The execution plan shows that the filtering happens first, followed by the offset and limit application.

    enter image description here

    here is ss of query analysis here you can see it will first apply condition and then will apply limit.to 20 records that we get form result.

    And also, it’s important to note that the actual performance and efficiency of this operation depend on various factors such as indexing also.

    I hope this explanation was helpful.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search