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

Commit6ef7714

Browse files
committed
> >> My question is whether postgres can index null values, and if not, do I
> >> have to accept a full table scan when locating records.> >> > It indexes them, but "is null" is not an indexable operator, so you> > can't directly solve the above with a 3-column index. What you can do> > instead is use a partial index, for instance> >> > create index i on CUSTOMER.WCCustOrderStatusLog (WCOrderStatusID)> > where Acknowledged is null and Processing is null;>> That's a very nifty trick and exactly the sort of answer I was after!Add CREATE INDEX doc mention of using partial indexes for IS NULLindexing; idea from Tom.
1 parent6949fc0 commit6ef7714

File tree

1 file changed

+12
-4
lines changed

1 file changed

+12
-4
lines changed

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

Lines changed: 12 additions & 4 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.44 2003/11/29 19:51:38 pgsql Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.45 2004/04/20 01:00:26 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -66,7 +66,7 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
6666
When the <literal>WHERE</literal> clause is present, a
6767
<firstterm>partial index</firstterm> is created.
6868
A partial index is an index that contains entries for only a portion of
69-
a table, usually a portion that issomehowmoreinteresting than the
69+
a table, usually a portion that is moreuseful for indexing than the
7070
rest of the table. For example, if you have a table that contains both
7171
billed and unbilled orders where the unbilled orders take up a small
7272
fraction of the total table and yet that is an often used section, you
@@ -76,10 +76,18 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
7676
table.
7777
</para>
7878

79+
<para>
80+
<literal>NULL</> values are not indexed by default. The best way
81+
to index <literal>NULL</> values is to create a partial index using
82+
an <literal>IS NULL</> comparison. <literal>IS NULL</> is more
83+
of a function call than a value comparison, and this is why a partial
84+
index works.
85+
</para>
86+
7987
<para>
8088
The expression used in the <literal>WHERE</literal> clause may refer
81-
only to columns of the underlying table (but it can use all columns,
82-
notonly theone(s) being indexed). Presently, subqueries and
89+
only to columns of the underlying table,but it can use all columns,
90+
notjust theones being indexed. Presently, subqueries and
8391
aggregate expressions are also forbidden in <literal>WHERE</literal>.
8492
The same restrictions apply to index fields that are expressions.
8593
</para>

0 commit comments

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