Thread: 'cost' and 'rows' for volitile function

pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly 
VOLATILE functions.  Is one hundred the new ninety-nine? and therefore 
these values are ignored by the planner?

Rob Sargent wrote:
> Richard Huxton wrote:
>> Rob Sargent wrote:
>>> pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly 
>>> VOLATILE functions. 

> Thanks for the pointer back to CREATE FUNCTION docs.  That would have 
> told me that those value are indeed the defaults.  I guess I'm somewhat 
> surprised that they are in fact defautled.  "COST" is too arbitrary for 
> me to comment on, but my functions' results sets fall on both sides of 
> 1000 rows, so I'm not sure giving this value to the planner does a lot 
> of good.  Luckily, my functions are typically pretty much stand-alone 
> calls, and not part of some larger query.

If your functions are all called at the top-level then indeed it doesn't 
matter. At a low level though, telling the planner function F1 costs 
1000 times more than F2 is useful.

--   Richard Huxton  Archonet Ltd

Rob Sargent wrote:
> Richard Huxton wrote:
>> If your functions are all called at the top-level then indeed it 
>> doesn't matter. At a low level though, telling the planner function F1 
>> costs 1000 times more than F2 is useful.
>>
> What scares me about this is that for function such as the ones I'm 
> currently using which return a wide range of result set size depending 
> on input values.   The planner will be mis-informed by a factor of 10 or 
> more quite easily.

Well, like I said - it doesn't matter as long as you are calling the 
function at the top level. The only option the planner has is "execute 
function".

However, it is a real problem for what you might term "irregularly 
shaped" functions where the costs/rows varies widely. I seem to recall a 
suggestion at one point that the values could be replaced by cost 
functions that get the same parameters as the function itself. However, 
that's could be expensive to do for every function-call, and especially 
since there's not always an obvious way to estimate # rows without 
calling the function first. I daresay someone will get around to 
handling both options in a clean way at some point, but the ability to 
define function costs at all was only added in 8.2 iirc.

--   Richard Huxton  Archonet Ltd