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

Postgresql – Why POSTGRES ORDER BY AND LIMIT WITH UUID so slow?

I have a problem with my request. I have a table where the id is UUID and I have a query like SELECT * FROM table WHERE fk = ? ORDER BY id ASC LIMIT 5 and this query runs like 50s but if I remove the order by id this query runs in 0.3s. But if I used some FK that is also UUID, order by FK work fast

Tags:

2

Answers


  1. 0

    The ORDER BY clause present in the first query (but not the second) adds an additional sorting step to the query pipeline. For a moderately large table, this sort can take some time, especially if your query does not have any indexing scheme to make it run faster. Your current observations are expected.

    Login or Signup to reply.
  2. 0

    With this little information, I can only guess that the optimizer opts to use the index that supports ORDER BY in the vain hope to find enough result rows quickly.

    You can disable that strategy with

    ORDER BY id ASC NULLS FIRST

    Unless you created the index with NULLS FIRST, it is created with NULLS LAST by default. But even if there are no NULL values, PostgreSQL won’t consider an index created with NULLS LAST for an ORDER BY clause with NULLS LAST.

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

Back To Top
Search