On Jan 20, 2008 9:34 AM, Heikki Linnakangas <heikki@xxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
