Rod Taylor <[EMAIL PROTECTED]> writes: > You can force it with a subselect though: > SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) > as distance > from zipcodes) AS tab where distance <= $dist;
The above will *not* stop zipdist from being run twice, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend