2009/8/2 Sam Mason <sam@xxxxxxxxxxxxx>: > 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. I did it - and in this case immutable is wrong and strict not. It's an new for me, because I used rules that are well only for plpgsql or C language. What I see now, the rules for sql are totally different. Pavel > > -- > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
