Re: Slow set-returning functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for the replies.

Converting the functions to plpgsql and using EXECUTE works a treat.
On the real data, one of my functions is now over 50x faster :-)

Dean


> Date: Sun, 20 Jan 2008 10:25:48 -0500
> From: mmoncure@xxxxxxxxx
> To: heikki@xxxxxxxxxxxxxxxx
> Subject: Re:  Slow set-returning functions
> CC: dean_rasheed@xxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx
>
> On Jan 20, 2008 9:34 AM, Heikki Linnakangas  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

_________________________________________________________________
Get Hotmail on your mobile, text MSN to 63463!
http://mobile.uk.msn.com/pc/mail.aspx
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux