[CC any replies, thanks] This is basically a refined version of the last one.
- It restricts itself to btree indexes - Doesn't play tricks with RestrictInfos and focuses on a new type of ScanKey. Thus, the EXPLAIN output looks correct now also. - Does IS NOT NULL as well as IS NULL - Probably better use of statistics. It's messier because now more places need to know about it, but functionalitywise it's like the last one. For the archive: http://svana.org/kleptog/pgsql/indexnulls3.diff -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Index: src/backend/access/nbtree/nbtsearch.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/nbtree/nbtsearch.c,v
retrieving revision 1.93
diff -u -r1.93 nbtsearch.c
--- src/backend/access/nbtree/nbtsearch.c 19 Jun 2005 22:41:00 -0000
1.93
+++ src/backend/access/nbtree/nbtsearch.c 21 Sep 2005 21:44:34 -0000
@@ -647,13 +647,6 @@
ScanKey cur = startKeys[i];
/*
- * _bt_preprocess_keys disallows it, but it's place to add some
- * code later
- */
- if (cur->sk_flags & SK_ISNULL)
- elog(ERROR, "btree doesn't support is(not)null, yet");
-
- /*
* If scankey operator is of default subtype, we can use the
* cached comparison procedure; otherwise gotta look it up in
the
* catalogs.
Index: src/backend/access/nbtree/nbtutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/nbtree/nbtutils.c,v
retrieving revision 1.63
diff -u -r1.63 nbtutils.c
--- src/backend/access/nbtree/nbtutils.c 13 Jun 2005 23:14:48 -0000
1.63
+++ src/backend/access/nbtree/nbtutils.c 21 Sep 2005 21:44:34 -0000
@@ -257,12 +257,23 @@
if (numberOfKeys == 1)
{
/*
- * We don't use indices for 'A is null' and 'A is not null'
- * currently and 'A < = > <> NULL' will always fail - so qual is
- * not OK if comparison value is NULL. - vadim 03/21/97
+ * A IS (NOT) NULL is transformed to scankey with the
+ * SK_INDEXFINDNULL flag set. In this case the strategy
+ * determines if it IS NULL or IS NOT NULL.
+ *
+ * Note the use of SK_INDEXFINDNULL. This flag needs to be
+ * set for the index to find NULLs. This is to distinguish
+ * the static predicates (a = NULL) and (a IS NULL) and the
+ * runtime predicate (a = col) where col happend to be NULL
+ * this iteration.
*/
if (cur->sk_flags & SK_ISNULL)
- so->qual_ok = false;
+ {
+ if( cur->sk_strategy != BTEqualStrategyNumber &&
cur->sk_strategy != BTLessStrategyNumber )
+ so->qual_ok = false;
+ else if( !(cur->sk_flags & SK_INDEXFINDNULL) )
+ so->qual_ok = false;
+ }
else if (relation->rd_index->indisunique &&
relation->rd_rel->relnatts == 1)
{
@@ -308,13 +319,18 @@
/* See comments above: any NULL implies cannot match
qual */
if (cur->sk_flags & SK_ISNULL)
{
- so->qual_ok = false;
-
- /*
- * Quit processing so we don't try to invoke
comparison
- * routines on NULLs.
- */
- return;
+ /* If it doesn't represent IS (NOT) NULL, it's
always false */
+ if( !(cur->sk_flags & SK_INDEXFINDNULL) )
+ {
+ so->qual_ok = false;
+ return;
+ }
+ /* Check for expected strategies */
+ if( cur->sk_strategy != BTEqualStrategyNumber
&& cur->sk_strategy != BTLessStrategyNumber )
+ {
+ elog( ERROR, "Unrecognised NullTest in
_bt_preprocesskeys" );
+ return;
+ }
}
}
@@ -345,6 +361,17 @@
if (!chk || j == (BTEqualStrategyNumber
- 1))
continue;
+
+ /* If we have a (col IS NULL), cannot
accept any other predicates */
+ if ( eq->sk_flags & SK_ISNULL )
+ {
+ so->qual_ok = false;
+ break;
+ }
+ /* And a (col IS NOT NULL) is redundant
with an equals */
+ if( chk->sk_flags & SK_ISNULL )
+ continue;
+
test = FunctionCall2(&chk->sk_func,
eq->sk_argument,
chk->sk_argument);
@@ -375,13 +402,19 @@
ScanKey lt = xform[BTLessStrategyNumber
- 1];
ScanKey le =
xform[BTLessEqualStrategyNumber - 1];
- test = FunctionCall2(&le->sk_func,
-
lt->sk_argument,
-
le->sk_argument);
- if (DatumGetBool(test))
- xform[BTLessEqualStrategyNumber - 1] =
NULL;
- else
+ /* An IS NOT NULL is redundant with le clause */
+ if( lt->sk_flags & SK_ISNULL )
xform[BTLessStrategyNumber - 1] = NULL;
+ else
+ {
+ test = FunctionCall2(&le->sk_func,
+
lt->sk_argument,
+
le->sk_argument);
+ if (DatumGetBool(test))
+ xform[BTLessEqualStrategyNumber
- 1] = NULL;
+ else
+ xform[BTLessStrategyNumber - 1]
= NULL;
+ }
}
/* keep only one of >, >= */
@@ -445,6 +478,46 @@
/* have we seen one of these before? */
if (xform[j])
{
+ /* If either arg is NULL, need special handling */
+ if ( (cur->sk_flags | xform[j]->sk_flags) & SK_ISNULL)
+ {
+ if ( j == (BTEqualStrategyNumber - 1) ) /* IS
NULL clause */
+ {
+ /* If matching NULL, we can *only*
match null */
+ if ( (cur->sk_flags ^
xform[j]->sk_flags) & SK_ISNULL)
+ {
+ so->qual_ok = false;
+ continue;
+ }
+ /* If both = NULL, goto next attr */
+ if( cur->sk_flags & SK_ISNULL )
+ {
+ continue;
+ }
+ /* Should never reach here */
+ }
+ /* IS NOT NULL is redundant with any other LT
clause */
+ if ( j == (BTLessStrategyNumber - 1) ) /* IS
NOT NULL clause */
+ {
+ /* Both IS NOT NULL, goto next attr */
+ if ( (cur->sk_flags &
xform[j]->sk_flags) & SK_ISNULL)
+ {
+ continue;
+ }
+ /* Keep the non-redundant one */
+ if( xform[j]->sk_flags & SK_ISNULL )
+ {
+ xform[j] = cur;
+ continue;
+ }
+ if( cur->sk_flags & SK_ISNULL )
+ {
+ continue;
+ }
+ }
+ /* Should never get here */
+ elog( ERROR, "Impossible case in
_bt_preprocess_keys" );
+ }
/* yup, keep the more restrictive key */
test = FunctionCall2(&cur->sk_func,
cur->sk_argument,
@@ -515,12 +588,29 @@
tupdesc,
&isNull);
- /* btree doesn't support 'A is null' clauses, yet */
- if (key->sk_flags & SK_ISNULL)
+ if (key->sk_flags & SK_ISNULL && key->sk_strategy ==
BTEqualStrategyNumber )
{
- /* we shouldn't get here, really; see
_bt_preprocess_keys() */
- *continuescan = false;
- return false;
+ /* This scankey is (col IS NULL). The comment below
+ * applies here too: if we're at a not null value on
+ * a required key, we must be at a point where it is
+ * no longer useful to proceed. */
+
+ if( !isNull && ikey < so->numberOfRequiredKeys )
+ *continuescan = false;
+
+ return isNull;
+ }
+
+ if (key->sk_flags & SK_ISNULL && key->sk_strategy ==
BTLessStrategyNumber )
+ {
+ /* This scankey is (col IS NOT NULL). As per comment
+ below, if we have a NULL value on a ForwardScan
+ we've reached the end of useful values.*/
+ if (ikey < so->numberOfRequiredKeys &&
+ ScanDirectionIsForward(dir))
+ *continuescan = false;
+
+ return !isNull;
}
if (isNull)
Index: src/backend/executor/nodeIndexscan.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/nodeIndexscan.c,v
retrieving revision 1.103
diff -u -r1.103 nodeIndexscan.c
--- src/backend/executor/nodeIndexscan.c 6 May 2005 17:24:54 -0000
1.103
+++ src/backend/executor/nodeIndexscan.c 21 Sep 2005 21:44:34 -0000
@@ -571,6 +571,38 @@
strategy_cell = lnext(strategy_cell);
subtype = lfirst_oid(subtype_cell);
subtype_cell = lnext(subtype_cell);
+
+ if (IsA(clause, NullTest))
+ {
+ leftop = (Expr *) ((NullTest *) clause)->arg;
+
+ if (leftop && IsA(leftop, RelabelType))
+ leftop = ((RelabelType *) leftop)->arg;
+
+ Assert(leftop != NULL);
+
+ if (!(IsA(leftop, Var) &&
+ var_is_rel((Var *) leftop)))
+ elog(ERROR, "NullTest doesn't have key");
+
+ varattno = ((Var *) leftop)->varattno;
+
+ /*
+ * We need SK_INDEXFINDNULL for IS NULL to work, for
+ * runtime keys a NULL is special and shouldn't
+ * match anything.
+ *
+ * Note: for IS (NOT) NULL no procedure is needed.
+ */
+ ScanKeyEntryInitializeWithInfo(&scan_keys[j],
+ SK_ISNULL |
SK_INDEXFINDNULL,
+ varattno,
/* attribute number to scan */
+ strategy,
/* op's strategy */
+ subtype,
/* strategy subtype */
+ NULL,
/* reg proc to use */
+ (Datum)0 );
/* constant */
+ continue;
+ }
if (!IsA(clause, OpExpr))
elog(ERROR, "indexqual is not an OpExpr");
Index: src/backend/optimizer/path/indxpath.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.188
diff -u -r1.188 indxpath.c
--- src/backend/optimizer/path/indxpath.c 28 Aug 2005 22:47:20 -0000
1.188
+++ src/backend/optimizer/path/indxpath.c 21 Sep 2005 21:44:34 -0000
@@ -816,6 +816,14 @@
if (match_boolean_index_clause((Node *) clause, indexcol,
index))
return true;
}
+
+ /* We can index NULL tests also, for btree only */
+ if( index->relam == BTREE_AM_OID && IsA( clause, NullTest ) )
+ {
+ leftop = (Node*) ((NullTest*)clause)->arg;
+ if (match_index_to_operand(leftop, indexcol, index))
+ return true;
+ }
/* Else clause must be a binary opclause. */
if (!is_opclause(clause))
@@ -1928,6 +1936,14 @@
continue;
}
}
+
+ /* IS (NOT) NULL case */
+ if( index->relam == BTREE_AM_OID && IsA( rinfo->clause,
NullTest ) )
+ {
+ resultquals = lappend(resultquals,
make_restrictinfo( rinfo->clause, true, NULL ) );
+
+ continue;
+ }
resultquals = list_concat(resultquals,
expand_indexqual_condition(rinfo,
Index: src/backend/optimizer/plan/createplan.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.197
diff -u -r1.197 createplan.c
--- src/backend/optimizer/plan/createplan.c 18 Aug 2005 17:51:11 -0000
1.197
+++ src/backend/optimizer/plan/createplan.c 21 Sep 2005 21:44:35 -0000
@@ -33,7 +33,7 @@
#include "parser/parse_expr.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
-
+#include "access/skey.h"
static Scan *create_scan_plan(PlannerInfo *root, Path *best_path);
static List *build_relation_tlist(RelOptInfo *rel);
@@ -1513,6 +1513,20 @@
Assert(IsA(rinfo, RestrictInfo));
clause = (OpExpr *) rinfo->clause;
+ if (IsA(clause, NullTest))
+ {
+ NullTest *newc = copyObject((Node *) clause);
+ newc->arg = (Expr *)fix_indexqual_operand( (Node
*)newc->arg, index, &opclass );
+ /* Add to fixed index quals */
+ *fixed_indexquals = lappend(*fixed_indexquals, newc);
+ /* IS NULL -> EqualStrategy, IS NOT NULL ->
LessStrategy */
+ *indexstrategy = lappend_int(*indexstrategy,
(newc->nulltesttype == IS_NULL) ? BTEqualStrategyNumber : BTLessStrategyNumber
);
+ /* No subtype */
+ *indexsubtype = lappend_oid(*indexsubtype, InvalidOid);
+ /* NullTests are never lossy */
+ *nonlossy_indexquals = lappend(*nonlossy_indexquals,
rinfo);
+ continue;
+ }
if (!IsA(clause, OpExpr) ||
list_length(clause->args) != 2)
elog(ERROR, "indexqual clause is not binary opclause");
Index: src/backend/utils/adt/selfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.187
diff -u -r1.187 selfuncs.c
--- src/backend/utils/adt/selfuncs.c 21 Jul 2005 04:41:43 -0000 1.187
+++ src/backend/utils/adt/selfuncs.c 21 Sep 2005 21:44:35 -0000
@@ -4373,6 +4373,12 @@
Assert(IsA(rinfo, RestrictInfo));
clause = rinfo->clause;
+ if( IsA(clause, NullTest))
+ {
+ /* NullTests don't need to do anything with commutators
*/
+ indexBoundQuals = lappend(indexBoundQuals, rinfo);
+ continue;
+ }
Assert(IsA(clause, OpExpr));
clause_op = ((OpExpr *) clause)->opno;
if (match_index_to_operand(get_leftop(clause), indexcol, index))
Index: src/include/access/skey.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/skey.h,v
retrieving revision 1.29
diff -u -r1.29 skey.h
--- src/include/access/skey.h 24 Jun 2005 00:18:52 -0000 1.29
+++ src/include/access/skey.h 21 Sep 2005 21:44:36 -0000
@@ -73,7 +73,7 @@
#define SK_ISNULL 0x0001 /* sk_argument is NULL */
#define SK_UNARY 0x0002 /* unary operator (currently
unsupported) */
#define SK_NEGATE 0x0004 /* must negate the function result */
-
+#define SK_INDEXFINDNULL 0x0008 /* index can find NULL with SK_ISNULL
here */
/*
* prototypes for functions in access/common/scankey.c
pgpOF53mo8s7w.pgp
Description: PGP signature
