To: Rod Taylor <pg@rbt.ca>
Cc: Bill Lawrence <bill.lawrence@cox.net>,
	Scott Marlowe <smarlowe@g2switchworks.com>,
	PFC <lists@boutiquenumerique.com>, pgsql-sql@postgresql.org
Subject: Re: Getting the output of a function used in a where clause 
In-reply-to: <1113876009.41948.1.camel@home> 
References: <NEBBJBFOALCOMIDOAMHCAENBDAAA.bill.lawrence@cox.net>
	<1113876009.41948.1.camel@home>
Comments: In-reply-to Rod Taylor <pg@rbt.ca>
	message dated "Mon, 18 Apr 2005 22:00:09 -0400"
Date: Mon, 18 Apr 2005 22:59:49 -0400
Message-ID: <13117.1113879589@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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