From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-committers(at)postgresql(dot)org |
Subject: | pgsql: Improve planner to drop constant-NULL inputs of AND/OR where it' |
Date: | 2014-04-29 17:13:03 |
Message-ID: | E1WfBaZ-0001nk-53@gemulon.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
Improve planner to drop constant-NULL inputs of AND/OR where it's legal.
In general we can't discard constant-NULL inputs, since they could change
the result of the AND/OR to be NULL. But at top level of WHERE, we do not
need to distinguish a NULL result from a FALSE result, so it's okay to
treat NULL as FALSE and then simplify AND/OR accordingly.
This is a very ancient oversight, but in 9.2 and later it can lead to
failure to optimize queries that previous releases did optimize, as a
result of more aggressive parameter substitution rules making it possible
to reduce more subexpressions to NULL constants. This is the root cause of
bug #10171 from Arnold Scheffler. We could alternatively have fixed that
by teaching orclauses.c to ignore constant-NULL OR arms, but it seems
better to get rid of them globally.
I resisted the temptation to back-patch this change into all active
branches, but it seems appropriate to back-patch as far as 9.2 so that
there will not be performance regressions of the kind shown in this bug.
Branch
------
REL9_3_STABLE
Details
-------
http://git.postgresql.org/pg/commitdiff/150a44e83d7559000aa9578d60c8906cae097f78
Modified Files
--------------
src/backend/optimizer/prep/prepqual.c | 79 +++++++++++++++++++++++-----
src/test/regress/expected/create_index.out | 11 ++++
src/test/regress/sql/create_index.sql | 7 +++
3 files changed, 85 insertions(+), 12 deletions(-)
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2014-04-30 01:35:49 | pgsql: Fix whitespace |
Previous Message | Greg Stark | 2014-04-29 11:44:49 | pgsql: Remove unnecessary cast causing a warning |