[PERFORM] STRICT SQL functions never inline
Folks, After having some production issues, I did some testing and it seems that any SQL function declared STRICT will never inline. As a result, it won't work with either indexes (on the underlying predicate) or partitioning. This seems like a horrible gotcha for our users. At the very least I'd like to document it (in CREATE FUNCTION, presumably), but it would be better to fix it. Thoughts? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] STRICT SQL functions never inline
On Tuesday, November 08, 2011 15:29:03 Josh Berkus wrote: Folks, After having some production issues, I did some testing and it seems that any SQL function declared STRICT will never inline. As a result, it won't work with either indexes (on the underlying predicate) or partitioning. This seems like a horrible gotcha for our users. At the very least I'd like to document it (in CREATE FUNCTION, presumably), but it would be better to fix it. Thoughts? I am all for documenting it somewhere. There were lots of people hit by it in the past - e.g. the postgis folks. Its not so easy to fix though. The problem is that straight inlining would change the behaviour because suddenly the expression might not return NULL anymore even though one of the parameters is NULL. Or even cause more problems because the content wasn't prepared to handle NULLs. It would be possible to inline a CASE $1 IS NULL OR $2 IS NULL THEN NULL ELSE orig_expression END but that would be usefull in far fewer cases because it won't help much in most cases and actually might hurt performance in some. Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] STRICT SQL functions never inline
Josh Berkus j...@agliodbs.com writes: After having some production issues, I did some testing and it seems that any SQL function declared STRICT will never inline. It won't unless the planner can prove that the resulting expression behaves the same, ie, is also strict for *all* the parameters. Which in most cases isn't true, or at least is very difficult to prove. This is not a bug. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
