From: Bill Lawrence <bill.lawrence@cox.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Rod Taylor <pg@rbt.ca>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: PFC <lists@boutiquenumerique.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting the output of a function used in a where clause
Date: Mon, 18 Apr 2005 21:08:22 -0700
Message-ID: <NEBBJBFOALCOMIDOAMHCMEOMDAAA.bill.lawrence@cox.net> (raw)
In-Reply-To: <13117.1113879589@sss.pgh.pa.us>
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) 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bill.lawrence@cox.net, tgl@sss.pgh.pa.us, pg@rbt.ca, smarlowe@g2switchworks.com, lists@boutiquenumerique.com
Subject: Re: Getting the output of a function used in a where clause
In-Reply-To: <NEBBJBFOALCOMIDOAMHCMEOMDAAA.bill.lawrence@cox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox