CODEWITHSUNDEEP
postgresqlquery-optimization
chris
chris

Reputation: 443

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

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.

Upvotes: 3

Views: 94

Answers (1)

jjanes
jjanes

Reputation: 44167

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.

Upvotes: 2

Related Questions