From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rod Taylor <pg@rbt.ca>
Cc: Bill Lawrence <bill.lawrence@cox.net>
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 22:59:49 -0400
Message-ID: <13117.1113879589@sss.pgh.pa.us> (raw)
In-Reply-To: <1113876009.41948.1.camel@home>
References: <NEBBJBFOALCOMIDOAMHCAENBDAAA.bill.lawrence@cox.net>
<1113876009.41948.1.camel@home>
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: tgl@sss.pgh.pa.us, pg@rbt.ca, bill.lawrence@cox.net, smarlowe@g2switchworks.com, lists@boutiquenumerique.com
Subject: Re: Getting the output of a function used in a where clause
In-Reply-To: <13117.1113879589@sss.pgh.pa.us>
* 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