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

Commitf02a82b

Browse files
committed
Make 'col IS NULL' clauses be indexable conditions.
Teodor Sigaev, with some kibitzing from Tom Lane.
1 parent146c83c commitf02a82b

File tree

19 files changed

+433
-111
lines changed

19 files changed

+433
-111
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.149 2007/04/02 03:49:36 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.150 2007/04/06 22:33:41 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -405,6 +405,13 @@
405405
<entry>Does the access method support null index entries?</entry>
406406
</row>
407407

408+
<row>
409+
<entry><structfield>amsearchnulls</structfield></entry>
410+
<entry><type>bool</type></entry>
411+
<entry></entry>
412+
<entry>Does the access method support IS NULL searches?</entry>
413+
</row>
414+
408415
<row>
409416
<entry><structfield>amstorage</structfield></entry>
410417
<entry><type>bool</type></entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 5 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.22 2007/02/22 22:00:22 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.23 2007/04/06 22:33:41 tgl Exp $ -->
22

33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
@@ -129,7 +129,10 @@
129129
It is, however, OK to omit rows where the first indexed column is null.
130130
Thus, <structfield>amindexnulls</structfield> should be set true only if the
131131
index access method indexes all rows, including arbitrary combinations of
132-
null values.
132+
null values. An index access method that sets
133+
<structfield>amindexnulls</structfield> may also set
134+
<structfield>amsearchnulls</structfield>, indicating that it supports
135+
<literal>IS NULL</> clauses as search conditions.
133136
</para>
134137

135138
</sect1>

‎doc/src/sgml/indices.sgml

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.70 2007/02/14 20:47:15 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.71 2007/04/06 22:33:41 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.(But note that<literal>IS NULL</>is not
151-
equivalent to <literal>=</> and is not indexable.)
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.
152152
</para>
153153

154154
<para>
@@ -180,8 +180,9 @@ 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. The following command is used to
184-
create a hash index:
183+
<literal>=</literal> operator. (But hash indexes do not support
184+
<literal>IS NULL</> searches.)
185+
The following command is used to create a hash index:
185186
<synopsis>
186187
CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING hash (<replaceable>column</replaceable>);
187188
</synopsis>
@@ -234,6 +235,8 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
234235

235236
(See <xref linkend="functions-geometry"> for the meaning of
236237
these operators.)
238+
Also, an <literal>IS NULL</> condition on
239+
an index column can be used with a GiST index.
237240
Many other GiST operator
238241
classes are available in the <literal>contrib</> collection or as separate
239242
projects. For more information see <xref linkend="GiST">.
@@ -266,6 +269,7 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
266269

267270
(See <xref linkend="functions-array"> for the meaning of
268271
these operators.)
272+
GIN indexes cannot use <literal>IS NULL</> as a search condition.
269273
Other GIN operator classes are available in the <literal>contrib</>
270274
<literal>tsearch2</literal> and <literal>intarray</literal> modules.
271275
For more information see <xref linkend="GIN">.

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 1 addition & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.61 2007/04/03 22:38:35 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.62 2007/04/06 22:33:41 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -434,12 +434,6 @@ Indexes:
434434
to remove an index.
435435
</para>
436436

437-
<para>
438-
Indexes are not used for <literal>IS NULL</> clauses by default.
439-
The best way to use indexes in such cases is to create a partial index
440-
using an <literal>IS NULL</> predicate.
441-
</para>
442-
443437
<para>
444438
Prior releases of <productname>PostgreSQL</productname> also had an
445439
R-tree index method. This method has been removed because

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

Lines changed: 10 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.29 2007/01/05 22:19:21 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/common/scankey.c,v 1.30 2007/04/06 22:33:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -20,7 +20,8 @@
2020
/*
2121
* ScanKeyEntryInitialize
2222
*Initializes a scan key entry given all the field values.
23-
*The target procedure is specified by OID.
23+
*The target procedure is specified by OID (but can be invalid
24+
*if SK_SEARCHNULL is set).
2425
*
2526
* Note: CurrentMemoryContext at call should be as long-lived as the ScanKey
2627
* itself, because that's what will be used for any subsidiary info attached
@@ -40,7 +41,13 @@ ScanKeyEntryInitialize(ScanKey entry,
4041
entry->sk_strategy=strategy;
4142
entry->sk_subtype=subtype;
4243
entry->sk_argument=argument;
43-
fmgr_info(procedure,&entry->sk_func);
44+
if (RegProcedureIsValid(procedure))
45+
fmgr_info(procedure,&entry->sk_func);
46+
else
47+
{
48+
Assert(flags&SK_SEARCHNULL);
49+
MemSet(&entry->sk_func,0,sizeof(entry->sk_func));
50+
}
4451
}
4552

4653
/*

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

Lines changed: 33 additions & 25 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.64 2007/01/20 18:43:35 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistget.c,v 1.65 2007/04/06 22:33:41 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -381,37 +381,45 @@ gistindex_keytest(IndexTuple tuple,
381381
if (key->sk_flags&SK_ISNULL)
382382
{
383383
/*
384-
* is the compared-to datum NULL? on non-leaf page it's possible
385-
* to have nulls in childs :(
384+
* On non-leaf page we can't conclude that child hasn't NULL
385+
* values because of assumption in GiST: uinon (VAL, NULL) is VAL
386+
* But if on non-leaf page key IS NULL then all childs
387+
* has NULL.
386388
*/
387389

388-
if (isNull|| !GistPageIsLeaf(p))
389-
return true;
390-
return false;
390+
Assert(key->sk_flags&SK_SEARCHNULL );
391+
392+
if (GistPageIsLeaf(p)&& !isNull )
393+
return false;
391394
}
392395
elseif (isNull)
396+
{
393397
return false;
398+
}
399+
else
400+
{
394401

395-
gistdentryinit(giststate,key->sk_attno-1,&de,
396-
datum,r,p,offset,
397-
FALSE,isNull);
402+
gistdentryinit(giststate,key->sk_attno-1,&de,
403+
datum,r,p,offset,
404+
FALSE,isNull);
398405

399-
/*
400-
* Call the Consistent function to evaluate the test. The arguments
401-
* are the index datum (as a GISTENTRY*), the comparison datum, and
402-
* the comparison operator's strategy number and subtype from pg_amop.
403-
*
404-
* (Presently there's no need to pass the subtype since it'll always
405-
* be zero, but might as well pass it for possible future use.)
406-
*/
407-
test=FunctionCall4(&key->sk_func,
408-
PointerGetDatum(&de),
409-
key->sk_argument,
410-
Int32GetDatum(key->sk_strategy),
411-
ObjectIdGetDatum(key->sk_subtype));
412-
413-
if (!DatumGetBool(test))
414-
return false;
406+
/*
407+
* Call the Consistent function to evaluate the test. The arguments
408+
* are the index datum (as a GISTENTRY*), the comparison datum, and
409+
* the comparison operator's strategy number and subtype from pg_amop.
410+
*
411+
* (Presently there's no need to pass the subtype since it'll always
412+
* be zero, but might as well pass it for possible future use.)
413+
*/
414+
test=FunctionCall4(&key->sk_func,
415+
PointerGetDatum(&de),
416+
key->sk_argument,
417+
Int32GetDatum(key->sk_strategy),
418+
ObjectIdGetDatum(key->sk_subtype));
419+
420+
if (!DatumGetBool(test))
421+
return false;
422+
}
415423

416424
keySize--;
417425
key++;

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

Lines changed: 1 addition & 3 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/nbtree/nbtsearch.c,v 1.111 2007/01/09 02:14:10 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsearch.c,v 1.112 2007/04/06 22:33:42 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -746,8 +746,6 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
746746
*
747747
* If goback = true, we will then step back one item, while if
748748
* goback = false, we will start the scan on the located item.
749-
*
750-
* it's yet other place to add some code later for is(not)null ...
751749
*----------
752750
*/
753751
switch (strat_total)

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

Lines changed: 77 additions & 16 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.83 2007/03/30 00:12:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.84 2007/04/06 22:33:42 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -264,12 +264,27 @@ _bt_preprocess_keys(IndexScanDesc scan)
264264
if (numberOfKeys==1)
265265
{
266266
/*
267-
* We don't use indices for 'A is null' and 'A is not null' currently
268-
* and 'A < = > <> NULL' will always fail - so qual is not OK if
269-
* comparison value is NULL. - vadim 03/21/97
267+
* We treat all btree operators as strict (even if they're not so
268+
* marked in pg_proc). This means that it is impossible for an
269+
* operator condition with a NULL comparison constant to succeed,
270+
* and we can reject it right away.
271+
*
272+
* However, we now also support "x IS NULL" clauses as search
273+
* conditions, so in that case keep going. The planner has not
274+
* filled in any particular strategy in this case, so set it to
275+
* BTEqualStrategyNumber --- we can treat IS NULL as an equality
276+
* operator for purposes of search strategy.
270277
*/
271278
if (cur->sk_flags&SK_ISNULL)
272-
so->qual_ok= false;
279+
{
280+
if (cur->sk_flags&SK_SEARCHNULL)
281+
{
282+
cur->sk_strategy=BTEqualStrategyNumber;
283+
cur->sk_subtype=InvalidOid;
284+
}
285+
else
286+
so->qual_ok= false;
287+
}
273288
_bt_mark_scankey_with_indoption(cur,indoption);
274289
memcpy(outkeys,cur,sizeof(ScanKeyData));
275290
so->numberOfKeys=1;
@@ -303,17 +318,20 @@ _bt_preprocess_keys(IndexScanDesc scan)
303318
{
304319
if (i<numberOfKeys)
305320
{
306-
/* See comments above: any NULL implies cannot match qual */
321+
/* See comments above about NULLs and IS NULL handling. */
307322
/* Note: we assume SK_ISNULL is never set in a row header key */
308323
if (cur->sk_flags&SK_ISNULL)
309324
{
310-
so->qual_ok= false;
311-
312-
/*
313-
* Quit processing so we don't try to invoke comparison
314-
* routines on NULLs.
315-
*/
316-
return;
325+
if (cur->sk_flags&SK_SEARCHNULL)
326+
{
327+
cur->sk_strategy=BTEqualStrategyNumber;
328+
cur->sk_subtype=InvalidOid;
329+
}
330+
else
331+
{
332+
so->qual_ok= false;
333+
return;
334+
}
317335
}
318336
}
319337

@@ -344,6 +362,14 @@ _bt_preprocess_keys(IndexScanDesc scan)
344362

345363
if (!chk||j== (BTEqualStrategyNumber-1))
346364
continue;
365+
366+
/* IS NULL together with any other predicate must fail */
367+
if (eq->sk_flags&SK_SEARCHNULL)
368+
{
369+
so->qual_ok= false;
370+
return;
371+
}
372+
347373
if (_bt_compare_scankey_args(scan,chk,eq,chk,
348374
&test_result))
349375
{
@@ -455,6 +481,23 @@ _bt_preprocess_keys(IndexScanDesc scan)
455481
else
456482
{
457483
/* yup, keep only the more restrictive key */
484+
485+
/* if either arg is NULL, don't try to compare */
486+
if ((cur->sk_flags |xform[j]->sk_flags)&SK_ISNULL)
487+
{
488+
/* at least one of them must be an IS NULL clause */
489+
Assert(j== (BTEqualStrategyNumber-1));
490+
Assert((cur->sk_flags |xform[j]->sk_flags)&SK_SEARCHNULL);
491+
/* if one is and one isn't, the search must fail */
492+
if ((cur->sk_flags ^xform[j]->sk_flags)&SK_SEARCHNULL)
493+
{
494+
so->qual_ok= false;
495+
return;
496+
}
497+
/* we have duplicate IS NULL clauses, ignore the newer one */
498+
continue;
499+
}
500+
458501
if (_bt_compare_scankey_args(scan,cur,cur,xform[j],
459502
&test_result))
460503
{
@@ -798,11 +841,29 @@ _bt_checkkeys(IndexScanDesc scan,
798841
tupdesc,
799842
&isNull);
800843

801-
/* btree doesn't support 'A is null' clauses, yet */
802844
if (key->sk_flags&SK_ISNULL)
803845
{
804-
/* we shouldn't get here, really; see _bt_preprocess_keys() */
805-
*continuescan= false;
846+
/* Handle IS NULL tests */
847+
Assert(key->sk_flags&SK_SEARCHNULL);
848+
849+
if (isNull)
850+
continue;/* tuple satisfies this qual */
851+
852+
/*
853+
* Tuple fails this qual. If it's a required qual for the current
854+
* scan direction, then we can conclude no further tuples will
855+
* pass, either.
856+
*/
857+
if ((key->sk_flags&SK_BT_REQFWD)&&
858+
ScanDirectionIsForward(dir))
859+
*continuescan= false;
860+
elseif ((key->sk_flags&SK_BT_REQBKWD)&&
861+
ScanDirectionIsBackward(dir))
862+
*continuescan= false;
863+
864+
/*
865+
* In any case, this indextuple doesn't match the qual.
866+
*/
806867
return false;
807868
}
808869

0 commit comments

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