pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSTRICT SQL functions never inline
3+ messages / 3 participants
[nested] [flat]
* STRICT SQL functions never inline
@ 2011-11-08 23:29 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 3+ messages in thread
From: Josh Berkus @ 2011-11-08 23:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: STRICT SQL functions never inline
@ 2011-11-09 00:25 Andres Freund <andres@anarazel.de>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Andres Freund @ 2011-11-09 00:25 UTC (permalink / raw)
To: pgsql-performance; +Cc: Josh Berkus <josh@agliodbs.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: STRICT SQL functions never inline
@ 2011-11-09 00:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2011-11-09 00:29 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus <josh@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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-09 00:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-08 23:29 STRICT SQL functions never inline Josh Berkus <josh@agliodbs.com>
2011-11-09 00:25 ` Andres Freund <andres@anarazel.de>
2011-11-09 00:29 ` Tom Lane <tgl@sss.pgh.pa.us>
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