DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=dQXUUvP399bhr8kID32OX8BnvIen+gWxxv5gEyK1ohHcCaoUhNQAArMf4qSUrgKZPI/VGqZIlEgJO9x21aCDhuS27j2qgtn6SINpfLvF1RthrZsyU2z6Zkni7Jq98Gvaj/7FZB4120MW4Y45oEu+34NL4F6efbqNCV9p/NFjmE8=
Message-ID: <b42b73150801200725y45600f6end8d234cea4f8ff22@mail.gmail.com>
Date: Sun, 20 Jan 2008 10:25:48 -0500
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Heikki Linnakangas" <heikki@enterprisedb.com>
Subject: Re: Slow set-returning functions
Cc: "Dean Rasheed" <dean_rasheed@hotmail.com>,
 pgsql-performance@postgresql.org
In-Reply-To: <47935C0C.4090908@enterprisedb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <BAY113-W283034364555E644CD1765F23C0@phx.gbl>
	<47935C0C.4090908@enterprisedb.com>

On Jan 20, 2008 9:34 AM, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
> Dean Rasheed wrote:
> > I have been having difficulty with some functions which return sets of
> > rows. The functions seem to run very slowly, even though the queries
> > they run execute very quicky if I run them directly from psgl.
> > Typically these queries are only returning a few hundred rows with my
> > real data.
> >
> > I have had difficulty coming up with a simple test case, but the code
> > below usually shows the same problem. Sometimes I have to run the
> > setup code a few times before it happens - not sure why (I would
> > expect this to be deterministic), but perhaps there is some randomness
> > introduced by the sampling done by the analyse.
> >
> > The function foo() which has a hard-coded LIMIT always executes
> > quickly (comparable to running the query directly).
> >
> > However, the function foo(int) which is passed the same LIMIT as a
> > parameter executes around 30 times slower. The only difference is that
> > the LIMIT is a parameter to the function, although the LIMIT isn't
> > reached anyway in this case. Sometimes running this same script
> > generates data for which this function executes as fast as the other
> > one (which is always fast).
>
> This is clearly because the planner doesn't know what the value for the
> parameter will be at run time, so it chooses a plan that's not optimal
> for LIMIT 100.
>
> > Is there any way that I can see what execution plan is being used
> > internally by the functions?

prepared statements have the same problem.  IIRC the planner assumes
10%, which will often drop to a seqscan or a bitmap index scan.  Some
years back I argued (unsuccessfully) to have the planner guess 100
rows or something like that.  Ideally, I think it would generate the
plan from the value passed into the first invocation of the function.

merlin