Re: Getting the output of a function used in a where clause

Rod Taylor <pg(at)rbt(dot)ca> 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