Improve planner to drop constant-NULL inputs of AND/OR where it's legal.
Tom Lane <tgl@sss.pgh.pa.us>
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.
Files
| Path | Change | +/− |
|---|---|---|
| src/backend/optimizer/prep/prepqual.c | modified | +67 −12 |
| src/test/regress/expected/create_index.out | modified | +11 −0 |
| src/test/regress/sql/create_index.sql | modified | +7 −0 |