postgresqlquery-optimization

Why is PostgreSQL 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;

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 a UUID, ORDER BY fk work fast.

Solution

  • 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 FIRST.