Thanks Tom and Rod.
There are indeed several additional conditions on the "real" query which
prune the search space (I formulate a quick search box and filter on
Lat/Lon's within the box). Since my user interface limits the search to a 30
mile radius, there are at most 81 results (in New York city, far fewer, for
other regions of the US), so I've elected to post process the results in my
script (calculating the distance for each returned record) and display only
the closest 20 results (I intended to use a LIMIT clause combined with an
ORDER BY in my SQL... LOL).
Again, Thanks for all the great advice!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, April 18, 2005 8:00 PM
To: Rod Taylor
Cc: Bill Lawrence; Scott Marlowe; PFC; pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting the output of a function used in a where clause
Rod Taylor <pg@rbt.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)
AStab 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)
AStab 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