Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit29c4ad9

Browse files
committed
Support "x IS NOT NULL" clauses as indexscan conditions. This turns out
to be just a minor extension of the previous patch that made "x IS NULL"indexable, because we can treat the IS NOT NULL condition as if it were"x < NULL" or "x > NULL" (depending on the index's NULLS FIRST/LAST option),just like IS NULL is treated like "x = NULL". Aside from any possibleusefulness in its own right, this is an important improvement forindex-optimized MAX/MIN aggregates: it is now reliably possible to geta column's min or max value cheaply, even when there are a lot of nullscluttering the interesting end of the index.
1 parent15faca2 commit29c4ad9

File tree

18 files changed

+295
-120
lines changed

18 files changed

+295
-120
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.215 2009/12/29 20:11:42 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.216 2010/01/01 21:53:48 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -466,7 +466,7 @@
466466
<entry><structfield>amsearchnulls</structfield></entry>
467467
<entry><type>bool</type></entry>
468468
<entry></entry>
469-
<entry>Does the access method support IS NULL searches?</entry>
469+
<entry>Does the access method support IS NULL/NOT NULL searches?</entry>
470470
</row>
471471

472472
<row>

‎doc/src/sgml/indexam.sgml‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.31 2009/07/29 20:56:17 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.32 2010/01/01 21:53:49 tgl Exp $ -->
22

33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
@@ -134,7 +134,8 @@
134134
null values. An index access method that sets
135135
<structfield>amindexnulls</structfield> may also set
136136
<structfield>amsearchnulls</structfield>, indicating that it supports
137-
<literal>IS NULL</> clauses as search conditions.
137+
<literal>IS NULL</> and <literal>IS NOT NULL</> clauses as search
138+
conditions.
138139
</para>
139140

140141
</sect1>

‎doc/src/sgml/indices.sgml‎

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.79 2009/08/07 20:54:31 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.80 2010/01/01 21:53:49 tgl Exp $ -->
22

33
<chapter id="indexes">
44
<title id="indexes-title">Indexes</title>
@@ -147,8 +147,8 @@ CREATE INDEX test1_id_index ON test1 (id);
147147

148148
Constructs equivalent to combinations of these operators, such as
149149
<literal>BETWEEN</> and <literal>IN</>, can also be implemented with
150-
a B-tree index search. Also, an <literal>IS NULL</>condition on
151-
an index column can be used with a B-tree index.
150+
a B-tree index search. Also, an <literal>IS NULL</>or <literal>IS NOT
151+
NULL</> condition onan index column can be used with a B-tree index.
152152
</para>
153153

154154
<para>
@@ -180,8 +180,7 @@ CREATE INDEX test1_id_index ON test1 (id);
180180
Hash indexes can only handle simple equality comparisons.
181181
The query planner will consider using a hash index whenever an
182182
indexed column is involved in a comparison using the
183-
<literal>=</literal> operator. (Hash indexes do not support
184-
<literal>IS NULL</> searches.)
183+
<literal>=</literal> operator.
185184
The following command is used to create a hash index:
186185
<synopsis>
187186
CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING hash (<replaceable>column</replaceable>);
@@ -1025,7 +1024,7 @@ SELECT am.amname AS index_method,
10251024
real statistics, some default values are assumed, which are
10261025
almost certain to be inaccurate. Examining an application's
10271026
index usage without having run <command>ANALYZE</command> is
1028-
therefore a lost cause.
1027+
therefore a lost cause.
10291028
See <xref linkend="vacuum-for-statistics" endterm="vacuum-for-statistics-title">
10301029
and <xref linkend="autovacuum" endterm="autovacuum-title"> for more information.
10311030
</para>

‎src/backend/access/common/scankey.c‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/common/scankey.c,v 1.32 2009/01/0117:23:34 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/common/scankey.c,v 1.33 2010/01/0121:53:49 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -21,7 +21,7 @@
2121
* ScanKeyEntryInitialize
2222
*Initializes a scan key entry given all the field values.
2323
*The target procedure is specified by OID (but can be invalid
24-
*if SK_SEARCHNULL is set).
24+
*if SK_SEARCHNULLor SK_SEARCHNOTNULLis set).
2525
*
2626
* Note: CurrentMemoryContext at call should be as long-lived as the ScanKey
2727
* itself, because that's what will be used for any subsidiary info attached
@@ -45,7 +45,7 @@ ScanKeyEntryInitialize(ScanKey entry,
4545
fmgr_info(procedure,&entry->sk_func);
4646
else
4747
{
48-
Assert(flags&SK_SEARCHNULL);
48+
Assert(flags&(SK_SEARCHNULL |SK_SEARCHNOTNULL));
4949
MemSet(&entry->sk_func,0,sizeof(entry->sk_func));
5050
}
5151
}

‎src/backend/access/gist/gistget.c‎

Lines changed: 14 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistget.c,v 1.82 2009/10/08 22:34:57 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistget.c,v 1.83 2010/01/01 21:53:49 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -413,14 +413,20 @@ gistindex_keytest(IndexTuple tuple,
413413
{
414414
/*
415415
* On non-leaf page we can't conclude that child hasn't NULL
416-
* values because of assumption in GiST:uinon (VAL, NULL) is VAL
417-
* But if on non-leaf page key ISNULL then allchilds has NULL.
416+
* values because of assumption in GiST:union (VAL, NULL) is VAL.
417+
* But if on non-leaf page key IS NULL, then allchildren are NULL.
418418
*/
419-
420-
Assert(key->sk_flags&SK_SEARCHNULL);
421-
422-
if (GistPageIsLeaf(p)&& !isNull)
423-
return false;
419+
if (key->sk_flags&SK_SEARCHNULL)
420+
{
421+
if (GistPageIsLeaf(p)&& !isNull)
422+
return false;
423+
}
424+
else
425+
{
426+
Assert(key->sk_flags&SK_SEARCHNOTNULL);
427+
if (isNull)
428+
return false;
429+
}
424430
}
425431
elseif (isNull)
426432
{

‎src/backend/access/gist/gistscan.c‎

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistscan.c,v 1.76 2009/06/11 14:48:53 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistscan.c,v 1.77 2010/01/01 21:53:49 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -92,15 +92,18 @@ gistrescan(PG_FUNCTION_ARGS)
9292
* field.
9393
*
9494
* Next, if any of keys is a NULL and that key is not marked with
95-
* SK_SEARCHNULL then nothing can be found.
95+
* SK_SEARCHNULL/SK_SEARCHNOTNULL then nothing can be found (ie,
96+
* we assume all indexable operators are strict).
9697
*/
9798
for (i=0;i<scan->numberOfKeys;i++)
9899
{
99-
scan->keyData[i].sk_func=so->giststate->consistentFn[scan->keyData[i].sk_attno-1];
100+
ScanKeyskey=&(scan->keyData[i]);
100101

101-
if (scan->keyData[i].sk_flags&SK_ISNULL)
102+
skey->sk_func=so->giststate->consistentFn[skey->sk_attno-1];
103+
104+
if (skey->sk_flags&SK_ISNULL)
102105
{
103-
if ((scan->keyData[i].sk_flags&SK_SEARCHNULL)==0)
106+
if (!(skey->sk_flags&(SK_SEARCHNULL |SK_SEARCHNOTNULL)))
104107
so->qual_ok= false;
105108
}
106109
}

‎src/backend/access/nbtree/nbtutils.c‎

Lines changed: 109 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.94 2009/10/08 22:34:57 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.95 2010/01/01 21:53:49 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -276,6 +276,11 @@ _bt_preprocess_keys(IndexScanDesc scan)
276276
* in any particular strategy in this case, so set it to
277277
* BTEqualStrategyNumber --- we can treat IS NULL as an equality
278278
* operator for purposes of search strategy.
279+
*
280+
* Likewise, "x IS NOT NULL" is supported. We treat that as either
281+
* "less than NULL" in a NULLS LAST index, or "greater than NULL"
282+
* in a NULLS FIRST index. However, we have to flip those around in
283+
* a DESC index, to allow for the re-flipping that occurs elsewhere.
279284
*/
280285
if (cur->sk_flags&SK_ISNULL)
281286
{
@@ -284,6 +289,21 @@ _bt_preprocess_keys(IndexScanDesc scan)
284289
cur->sk_strategy=BTEqualStrategyNumber;
285290
cur->sk_subtype=InvalidOid;
286291
}
292+
elseif (cur->sk_flags&SK_SEARCHNOTNULL)
293+
{
294+
switch (indoption[cur->sk_attno-1]&
295+
(INDOPTION_DESC |INDOPTION_NULLS_FIRST))
296+
{
297+
case0:/* ASC / NULLS LAST */
298+
caseINDOPTION_DESC |INDOPTION_NULLS_FIRST:
299+
cur->sk_strategy=BTLessStrategyNumber;
300+
break;
301+
default:
302+
cur->sk_strategy=BTGreaterStrategyNumber;
303+
break;
304+
}
305+
cur->sk_subtype=InvalidOid;
306+
}
287307
else
288308
so->qual_ok= false;
289309
}
@@ -320,7 +340,7 @@ _bt_preprocess_keys(IndexScanDesc scan)
320340
{
321341
if (i<numberOfKeys)
322342
{
323-
/* See comments above about NULLs and IS NULLhandling. */
343+
/* See comments above about NULLs and IS NULL/NOT NULLhandling */
324344
/* Note: we assume SK_ISNULL is never set in a row header key */
325345
if (cur->sk_flags&SK_ISNULL)
326346
{
@@ -329,6 +349,21 @@ _bt_preprocess_keys(IndexScanDesc scan)
329349
cur->sk_strategy=BTEqualStrategyNumber;
330350
cur->sk_subtype=InvalidOid;
331351
}
352+
elseif (cur->sk_flags&SK_SEARCHNOTNULL)
353+
{
354+
switch (indoption[cur->sk_attno-1]&
355+
(INDOPTION_DESC |INDOPTION_NULLS_FIRST))
356+
{
357+
case0:/* ASC / NULLS LAST */
358+
caseINDOPTION_DESC |INDOPTION_NULLS_FIRST:
359+
cur->sk_strategy=BTLessStrategyNumber;
360+
break;
361+
default:
362+
cur->sk_strategy=BTGreaterStrategyNumber;
363+
break;
364+
}
365+
cur->sk_subtype=InvalidOid;
366+
}
332367
else
333368
{
334369
so->qual_ok= false;
@@ -365,13 +400,6 @@ _bt_preprocess_keys(IndexScanDesc scan)
365400
if (!chk||j== (BTEqualStrategyNumber-1))
366401
continue;
367402

368-
/* IS NULL together with any other predicate must fail */
369-
if (eq->sk_flags&SK_SEARCHNULL)
370-
{
371-
so->qual_ok= false;
372-
return;
373-
}
374-
375403
if (_bt_compare_scankey_args(scan,chk,eq,chk,
376404
&test_result))
377405
{
@@ -484,23 +512,6 @@ _bt_preprocess_keys(IndexScanDesc scan)
484512
else
485513
{
486514
/* yup, keep only the more restrictive key */
487-
488-
/* if either arg is NULL, don't try to compare */
489-
if ((cur->sk_flags |xform[j]->sk_flags)&SK_ISNULL)
490-
{
491-
/* at least one of them must be an IS NULL clause */
492-
Assert(j== (BTEqualStrategyNumber-1));
493-
Assert((cur->sk_flags |xform[j]->sk_flags)&SK_SEARCHNULL);
494-
/* if one is and one isn't, the search must fail */
495-
if ((cur->sk_flags ^xform[j]->sk_flags)&SK_SEARCHNULL)
496-
{
497-
so->qual_ok= false;
498-
return;
499-
}
500-
/* we have duplicate IS NULL clauses, ignore the newer one */
501-
continue;
502-
}
503-
504515
if (_bt_compare_scankey_args(scan,cur,cur,xform[j],
505516
&test_result))
506517
{
@@ -534,8 +545,7 @@ _bt_preprocess_keys(IndexScanDesc scan)
534545
}
535546

536547
/*
537-
* Compare two scankey values using a specified operator. Both values
538-
* must be already known non-NULL.
548+
* Compare two scankey values using a specified operator.
539549
*
540550
* The test we want to perform is logically "leftarg op rightarg", where
541551
* leftarg and rightarg are the sk_argument values in those ScanKeys, and
@@ -555,8 +565,7 @@ _bt_preprocess_keys(IndexScanDesc scan)
555565
*
556566
* Note: this routine needs to be insensitive to any DESC option applied
557567
* to the index column. For example, "x < 4" is a tighter constraint than
558-
* "x < 5" regardless of which way the index is sorted. We don't worry about
559-
* NULLS FIRST/LAST either, since the given values are never nulls.
568+
* "x < 5" regardless of which way the index is sorted.
560569
*/
561570
staticbool
562571
_bt_compare_scankey_args(IndexScanDescscan,ScanKeyop,
@@ -571,6 +580,64 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op,
571580
cmp_op;
572581
StrategyNumberstrat;
573582

583+
/*
584+
* First, deal with cases where one or both args are NULL. This should
585+
* only happen when the scankeys represent IS NULL/NOT NULL conditions.
586+
*/
587+
if ((leftarg->sk_flags |rightarg->sk_flags)&SK_ISNULL)
588+
{
589+
boolleftnull,
590+
rightnull;
591+
592+
if (leftarg->sk_flags&SK_ISNULL)
593+
{
594+
Assert(leftarg->sk_flags& (SK_SEARCHNULL |SK_SEARCHNOTNULL));
595+
leftnull= true;
596+
}
597+
else
598+
leftnull= false;
599+
if (rightarg->sk_flags&SK_ISNULL)
600+
{
601+
Assert(rightarg->sk_flags& (SK_SEARCHNULL |SK_SEARCHNOTNULL));
602+
rightnull= true;
603+
}
604+
else
605+
rightnull= false;
606+
607+
/*
608+
* We treat NULL as either greater than or less than all other values.
609+
* Since true > false, the tests below work correctly for NULLS LAST
610+
* logic. If the index is NULLS FIRST, we need to flip the strategy.
611+
*/
612+
strat=op->sk_strategy;
613+
if (op->sk_flags&SK_BT_NULLS_FIRST)
614+
strat=BTCommuteStrategyNumber(strat);
615+
616+
switch (strat)
617+
{
618+
caseBTLessStrategyNumber:
619+
*result= (leftnull<rightnull);
620+
break;
621+
caseBTLessEqualStrategyNumber:
622+
*result= (leftnull <=rightnull);
623+
break;
624+
caseBTEqualStrategyNumber:
625+
*result= (leftnull==rightnull);
626+
break;
627+
caseBTGreaterEqualStrategyNumber:
628+
*result= (leftnull >=rightnull);
629+
break;
630+
caseBTGreaterStrategyNumber:
631+
*result= (leftnull>rightnull);
632+
break;
633+
default:
634+
elog(ERROR,"unrecognized StrategyNumber: %d", (int)strat);
635+
*result= false;/* keep compiler quiet */
636+
break;
637+
}
638+
return true;
639+
}
640+
574641
/*
575642
* The opfamily we need to worry about is identified by the index column.
576643
*/
@@ -844,11 +911,18 @@ _bt_checkkeys(IndexScanDesc scan,
844911

845912
if (key->sk_flags&SK_ISNULL)
846913
{
847-
/* Handle IS NULL tests */
848-
Assert(key->sk_flags&SK_SEARCHNULL);
849-
850-
if (isNull)
851-
continue;/* tuple satisfies this qual */
914+
/* Handle IS NULL/NOT NULL tests */
915+
if (key->sk_flags&SK_SEARCHNULL)
916+
{
917+
if (isNull)
918+
continue;/* tuple satisfies this qual */
919+
}
920+
else
921+
{
922+
Assert(key->sk_flags&SK_SEARCHNOTNULL);
923+
if (!isNull)
924+
continue;/* tuple satisfies this qual */
925+
}
852926

853927
/*
854928
* Tuple fails this qual. If it's a required qual for the current

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp