pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed'cost' and 'rows' for volitile function
4+ messages / 2 participants
[nested] [flat]
* 'cost' and 'rows' for volitile function
@ 2009-06-08 16:26 Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rob Sargent @ 2009-06-08 16:26 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] 'cost' and 'rows' for volitile function
@ 2009-06-08 17:16 Richard Huxton <dev@archonet.com>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2009-06-08 17:16 UTC (permalink / raw)
To: PgSql General <pgsql-general@postgresql.org>
Old fool that I am - forgot to cc: the list.
Richard Huxton wrote:
> Rob Sargent wrote:
>> 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?
>
> No, they're the default expected cost and number of rows returned when
> calling your functions. What do you think VOLATILE should be doing to them?
>
> http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 'cost' and 'rows' for volitile function
@ 2009-06-08 17:34 Richard Huxton <dev@archonet.com>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2009-06-08 17:34 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 'cost' and 'rows' for volitile function
@ 2009-06-08 17:57 Richard Huxton <dev@archonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2009-06-08 17:57 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-06-08 17:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-08 16:26 'cost' and 'rows' for volitile function Rob Sargent <robjsargent@gmail.com>
2009-06-08 17:16 ` Richard Huxton <dev@archonet.com>
2009-06-08 17:34 ` Richard Huxton <dev@archonet.com>
2009-06-08 17:57 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox