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

When a SQL LIMIT clause executes? (Postgresql)

I’m using Postgres. Suppose I have only one record that satisfies the query parameters. My friend claims that I can speed up query completion if I use limit=1; This is true? GPT Chat also states that the database will stop searching once the limit is met. But I couldn’t find any confirmation by googling.

I was also sure that the limit is applied only at the very end of the query execution, after the records have been formed.

Tags:

2

Answers


  1. 0

    First of all, using LIMIT <N> without ORDER BY is fairly meaningless, as there needs to be some logical order from which to select/retain the top N records.

    The LIMIT operation is evaluated dead last in the query pipeline, even after ORDER BY. In the case of your query, which generates just a single record result set, it should not even matter if you add an ORDER BY and LIMIT, because:

    • Since there is just one record, the ORDER BY sort will be skipped
    • Since there is just one record, doing LIMIT 1 will also be skipped

    In general, for a non empty result set, adding an ORDER BY clause will increase running time, because it instructs Postgres to do a sort operation.

    Login or Signup to reply.
  2. 0

    I comcur with Tim that LIMIT without ORDER BY is pretty meaningless. Also, LIMIT is normally executed as the last step of the query (but it will keep subsequent results from being computed).

    Anyway, while adding LIMIT to a query that has only a single result row won’t change the query result, it can influence the way in which PostgreSQL computes the result, so it can influence the performance of the query (the execution plan). With LIMIT, PostgreSQL will prefer execution plans that return the first rows quickly.

    If everything is as it should be, PostgreSQL will estimate the result row count correctly and choose the best execution plan, regardless if you use LIMIT or not. But the optimizer can get it wrong, and then the LIMIT can be a hint to get it on the right track.

    In case of doubt, examine the execution plans with EXPLAIN.

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

Back To Top
Search