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
