On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote: > 2009/8/2 Sam Mason <sam@xxxxxxxxxxxxx>: > > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote: > >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer > >> AS 'SELECT $1 / NULLIF($2,0);' > >> LANGUAGE SQL > >> IMMUTABLE > >> RETURNS NULL ON NULL INPUT; > > > > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used > > to think of it as just a "hint" to the planner as to its behavior, > > but it turns out that it's interpreted much more strongly by PG. The > > interpretation means that the function doesn't end up getting be inlined > > where I'd expect it to be and hence the optimizer doesn't get as much > > freedom to rewrite your queries as you may want. > > I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT > flag, and it means, don't run function, when any param is null. Yes, this is how PG interprets it. > For > optimalisator it means only one - when any parameter is constant NULL, > then function evaluation should be replaced by NULL. But not too much > often optimalizer should detect this case, so this is shortcut for > evaluator. This flag doesn't change inlining. No, not unless things have changed since this discussion: http://archives.postgresql.org/message-id/20090604090045.GR5407@xxxxxxxxxxxxx > > Admittedly it's going to be less of an issue with division that other > > operators, but it's worth bearing in mind. The "IMMUTABLE" options is a > > good one to specify though, keep that! > > There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug Not in any tests I've done. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
