Re: [HACKERS] function optimization ???

[Brent Verner]

On 24 Jan 2001 at 12:14 (-0500), Tom Lane wrote:
| Brent Verner <[EMAIL PROTECTED]> writes:
| > calling it as:
| >   SELECT p.*, p.book_info.title FROM pricing p WHERE vista_ans='POD';
| > background and observation:
| >   the pricing table is fairly large, but only a small number meet
| >   "WHERE vista_ans='POD'". I can select all where vista_ans='POD'
| >   very quickly (.2 sec), but adding in the get_book(pricing) call
| >   slows this down to about 20sec. I can, with an external sql query,
| >   select all of the desired records in about 1 sec, so it appears
| >   to me that the function is being called regardless of whether
| >   or not the WHERE clause is being satisfied.
| 
| This conclusion is absolutely false: the SELECT target list is NOT
| evaluated except at rows where the WHERE condition is satisfied.
| 
| I suspect the real problem is that the select inside the function
| is not being done as efficiently as you'd like.

yes, this is indeed the case. Sorry for the noise, my 'with an external
query' case was a broken product of sleep-dep :\.

thanks.
  brent

Re: [HACKERS] function optimization ???

[Tom Lane]

Brent Verner <[EMAIL PROTECTED]> writes:
> calling it as:
>   SELECT p.*, p.book_info.title FROM pricing p WHERE vista_ans='POD';
> background and observation:
>   the pricing table is fairly large, but only a small number meet
>   "WHERE vista_ans='POD'". I can select all where vista_ans='POD'
>   very quickly (.2 sec), but adding in the get_book(pricing) call
>   slows this down to about 20sec. I can, with an external sql query,
>   select all of the desired records in about 1 sec, so it appears
>   to me that the function is being called regardless of whether
>   or not the WHERE clause is being satisfied.

This conclusion is absolutely false: the SELECT target list is NOT
evaluated except at rows where the WHERE condition is satisfied.

I suspect the real problem is that the select inside the function
is not being done as efficiently as you'd like.  How big is
catalog_general, and would a sequential scan over it inside the
function account for the performance discrepancy?

IIRC, 7.0.* is not very bright about using indexscans in situations
where the righthand side of the WHERE clause is anything more complex
than a literal constant or simple parameter reference ($n).  The
fieldselect you have here would be enough to defeat the indexscan
recognizer.  This is fixed in 7.1, however.  For now, you could
declare book_info as taking a simple datum and invoke it as
p.vista_isbn.book_info.title

BTW, star_isbn and vista_isbn are the same datatype, I trust, else
that might cause failure to use an indexscan too.

regards, tom lane

[HACKERS] function optimization ???

[Brent Verner]

Hi,

I've the following function:

  CREATE FUNCTION book_info(pricing)
  RETURNS catalog_general AS '
select *
from catalog_general
where star_isbn = $1.vista_isbn
  ' LANGUAGE 'sql';

calling it as:
  
  SELECT p.*, p.book_info.title FROM pricing p WHERE vista_ans='POD';

background and observation:
  
  the pricing table is fairly large, but only a small number meet
  "WHERE vista_ans='POD'". I can select all where vista_ans='POD'
  very quickly (.2 sec), but adding in the get_book(pricing) call
  slows this down to about 20sec. I can, with an external sql query,
  select all of the desired records in about 1 sec, so it appears
  to me that the function is being called regardless of whether
  or not the WHERE clause is being satisfied.

question:
  
  is there any way the function call could be _not_ called if:
1) the WHERE clause does not reference any of its return values, and
2) the WHERE clause has already been satisified.

  ???

If this behavior is reasonable, could someone point me _toward_ the
code where I'd need to make this optimization. I think this would be
nice to have for 7.2 :)

brent