In PostgreSQL, are all expressions in ORDER BY clause evaluated?

user_7201547 0
Viewed 43

I'm looking to optimise a PostgreSQL query of mine and couldn't find anything on the internal workings of the query planner when it comes to ORDER BY.

Consider the following PostgreSQL query:

SELECT *
FROM mytable
ORDER BY rank, ST_Distance(geom1, geom2)

Is ST_Distance(geom1, geom2) calculated for all rows in mytable, even where rank is unique?

This is not a postgis question. ST_Distance(geom1, geom2) could be any expression that requires (expensive) computations.

query-optimization postgresql
1 Answers

All are evaluated. PostgreSQL's sort projects all the expressions it needs to do the comparison up front and stores the results. It does not defer the expression evaluation until there is a tie to break.

user_17212390
Related