diff options
| -rw-r--r-- | src/backend/optimizer/prep/prepqual.c | 79 | ||||
| -rw-r--r-- | src/test/regress/expected/create_index.out | 11 | ||||
| -rw-r--r-- | src/test/regress/sql/create_index.sql | 7 |
3 files changed, 85 insertions, 12 deletions
diff --git a/src/backend/optimizer/prep/prepqual.c b/src/backend/optimizer/prep/prepqual.c index 3760fdc645f..e7a6c801319 100644 --- a/src/backend/optimizer/prep/prepqual.c +++ b/src/backend/optimizer/prep/prepqual.c @@ -293,7 +293,7 @@ canonicalize_qual(Expr *qual) /* * Pull up redundant subclauses in OR-of-AND trees. We do this only * within the top-level AND/OR structure; there's no point in looking - * deeper. + * deeper. Also remove any NULL constants in the top-level structure. */ newqual = find_duplicate_ors(qual); @@ -393,6 +393,13 @@ pull_ors(List *orlist) * OR clauses to which the inverse OR distributive law might apply. * Only the top-level AND/OR structure is searched. * + * While at it, we remove any NULL constants within the top-level AND/OR + * structure, eg "x OR NULL::boolean" is reduced to "x". In general that + * would change the result, so eval_const_expressions can't do it; but at + * top level of WHERE, we don't need to distinguish between FALSE and NULL + * results, so it's valid to treat NULL::boolean the same as FALSE and then + * simplify AND/OR accordingly. + * * Returns the modified qualification. AND/OR flatness is preserved. */ static Expr * @@ -405,12 +412,30 @@ find_duplicate_ors(Expr *qual) /* Recurse */ foreach(temp, ((BoolExpr *) qual)->args) - orlist = lappend(orlist, find_duplicate_ors(lfirst(temp))); + { + Expr *arg = (Expr *) lfirst(temp); - /* - * Don't need pull_ors() since this routine will never introduce an OR - * where there wasn't one before. - */ + arg = find_duplicate_ors(arg); + + /* Get rid of any constant inputs */ + if (arg && IsA(arg, Const)) + { + Const *carg = (Const *) arg; + + /* Drop constant FALSE or NULL */ + if (carg->constisnull || !DatumGetBool(carg->constvalue)) + continue; + /* constant TRUE, so OR reduces to TRUE */ + return arg; + } + + orlist = lappend(orlist, arg); + } + + /* Flatten any ORs pulled up to just below here */ + orlist = pull_ors(orlist); + + /* Now we can look for duplicate ORs */ return process_duplicate_ors(orlist); } else if (and_clause((Node *) qual)) @@ -420,10 +445,38 @@ find_duplicate_ors(Expr *qual) /* Recurse */ foreach(temp, ((BoolExpr *) qual)->args) - andlist = lappend(andlist, find_duplicate_ors(lfirst(temp))); + { + Expr *arg = (Expr *) lfirst(temp); + + arg = find_duplicate_ors(arg); + + /* Get rid of any constant inputs */ + if (arg && IsA(arg, Const)) + { + Const *carg = (Const *) arg; + + /* Drop constant TRUE */ + if (!carg->constisnull && DatumGetBool(carg->constvalue)) + continue; + /* constant FALSE or NULL, so AND reduces to FALSE */ + return (Expr *) makeBoolConst(false, false); + } + + andlist = lappend(andlist, arg); + } + /* Flatten any ANDs introduced just below here */ andlist = pull_ands(andlist); - /* The AND list can't get shorter, so result is always an AND */ + + /* AND of no inputs reduces to TRUE */ + if (andlist == NIL) + return (Expr *) makeBoolConst(true, false); + + /* Single-expression AND just reduces to that expression */ + if (list_length(andlist) == 1) + return (Expr *) linitial(andlist); + + /* Else we still need an AND node */ return make_andclause(andlist); } else @@ -447,11 +500,13 @@ process_duplicate_ors(List *orlist) List *neworlist; ListCell *temp; + /* OR of no inputs reduces to FALSE */ if (orlist == NIL) - return NULL; /* probably can't happen */ - if (list_length(orlist) == 1) /* single-expression OR (can this - * happen?) */ - return linitial(orlist); + return (Expr *) makeBoolConst(false, false); + + /* Single-expression OR just reduces to that expression */ + if (list_length(orlist) == 1) + return (Expr *) linitial(orlist); /* * Choose the shortest AND clause as the reference list --- obviously, any diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index da3bdfbe07c..650197a7846 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2741,3 +2741,14 @@ ORDER BY thousand; 1 | 1001 (2 rows) +-- +-- Check elimination of constant-NULL subexpressions +-- +explain (costs off) + select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); + QUERY PLAN +------------------------------------------------------ + Index Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand = 1) AND (tenthous = 1001)) +(2 rows) + diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 6835c884c6e..c7644c0fb2a 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -915,3 +915,10 @@ ORDER BY thousand; SELECT thousand, tenthous FROM tenk1 WHERE thousand < 2 AND tenthous IN (1001,3000) ORDER BY thousand; + +-- +-- Check elimination of constant-NULL subexpressions +-- + +explain (costs off) + select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); |