Skip site navigation (1) Skip section navigation (2)

function optimization ???

From: Brent Verner <brent(at)rcfile(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: function optimization ???
Date: 2001-01-24 13:54:38
Message-ID: 20010124085438.A21448@rcfile.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: Zeugswetter Andreas SBDate: 2001-01-24 14:18:56
Subject: AW: Postgresql on win32
Previous:From: Bruce MomjianDate: 2001-01-24 13:52:13
Subject: Re: PgAccess - small bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group