Re: [PERFORM] Feature request: smarter use of conditional indexes

John Siracusa <siracusa(at)mindspring(dot)com> writes:
> How does this look? It seems to do what I want without horribly
> breaking anything as far as I can tell.

Not a bad effort for a backend newbie ;-). It was lacking in comments,
and upon inspection I thought it could be generalized a little. I've
applied the attached modification, which will recognize strict operators
and functions of any number of arguments.

regards, tom lane

*** src/backend/optimizer/path/indxpath.c.orig Wed Jan 7 17:02:48 2004
--- src/backend/optimizer/path/indxpath.c Sun Mar 7 00:13:27 2004
***************
*** 965,988 ****
};


! /*
* pred_test_simple_clause
* Does the "predicate inclusion test" for a "simple clause" predicate
* and a "simple clause" restriction.
*
! * We have two strategies for determining whether one simple clause
! * implies another. A simple and general way is to see if they are
! * equal(); this works for any kind of expression. (Actually, there
! * is an implied assumption that the functions in the expression are
! * immutable, ie dependent only on their input arguments --- but this
! * was checked for the predicate by CheckPredicate().)
*
! * Our other way works only for (binary boolean) operators that are
! * in some btree operator class. We use the above operator implication
! * table to be able to derive implications between nonidentical clauses.
*
! * Eventually, rtree operators could also be handled by defining an
! * appropriate "RT_implic_table" array.
*/
static bool
pred_test_simple_clause(Expr *predicate, Node *clause)
--- 965,1002 ----
};


! /*----------
* pred_test_simple_clause
* Does the "predicate inclusion test" for a "simple clause" predicate
* and a "simple clause" restriction.
*
! * We have three strategies for determining whether one simple clause
! * implies another:
*
! * A simple and general way is to see if they are equal(); this works for any
! * kind of expression. (Actually, there is an implied assumption that the
! * functions in the expression are immutable, ie dependent only on their input
! * arguments --- but this was checked for the predicate by CheckPredicate().)
*
! * When the predicate is of the form "foo IS NOT NULL", we can conclude that
! * the predicate is implied if the clause is a strict operator or function
! * that has "foo" as an input. In this case the clause must yield NULL when
! * "foo" is NULL, which we can take as equivalent to FALSE because we know
! * we are within an AND/OR subtree of a WHERE clause. (Again, "foo" is
! * already known immutable, so the clause will certainly always fail.)
! *
! * Our other way works only for binary boolean opclauses of the form
! * "foo op constant", where "foo" is the same in both clauses. The operators
! * and constants can be different but the operators must be in the same btree
! * operator class. We use the above operator implication table to be able to
! * derive implications between nonidentical clauses. (Note: "foo" is known
! * immutable, and constants are surely immutable, and we assume that operators
! * that are in btree opclasses are immutable, so there's no need to do extra
! * mutability checks in this case either.)
! *
! * Eventually, rtree operators could also be handled by defining an
! * appropriate "RT_implic_table" array.
! *----------
*/
static bool
pred_test_simple_clause(Expr *predicate, Node *clause)
***************
*** 1019,1024 ****
--- 1033,1055 ----
/* First try the equal() test */
if (equal((Node *) predicate, clause))
return true;
+
+ /* Next try the IS NOT NULL case */
+ if (predicate && IsA(predicate, NullTest) &&
+ ((NullTest *) predicate)->nulltesttype == IS_NOT_NULL)
+ {
+ Expr *nonnullarg = ((NullTest *) predicate)->arg;
+
+ if (is_opclause(clause) &&
+ member(nonnullarg, ((OpExpr *) clause)->args) &&
+ op_strict(((OpExpr *) clause)->opno))
+ return true;
+ if (is_funcclause(clause) &&
+ member(nonnullarg, ((FuncExpr *) clause)->args) &&
+ func_strict(((FuncExpr *) clause)->funcid))
+ return true;
+ return false; /* we can't succeed below... */
+ }

/*
* Can't do anything more unless they are both binary opclauses with a