Re: Index on a NULL-value

От: Tom Lane
Тема: Re: Index on a NULL-value
Дата: ,
Msg-id: 7923.1117513113@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Index on a NULL-value  (Bruno Wolff III)
Ответы: Re: Index on a NULL-value  (Tobias Brox)
Список: pgsql-performance

Скрыть дерево обсуждения

Index on a NULL-value  (Tobias Brox, )
 Re: Index on a NULL-value  (Bruno Wolff III, )
  Re: Index on a NULL-value  (Tobias Brox, )
   Re: Index on a NULL-value  (Bruno Wolff III, )
 Re: Index on a NULL-value  (Tobias Brox, )
  Re: Index on a NULL-value  (Bruno Wolff III, )
   Re: Index on a NULL-value  (Tobias Brox, )
    Re: Index on a NULL-value  (Greg Stark, )
     Re: Index on a NULL-value  (Tom Lane, )
   Re: Index on a NULL-value  (Tom Lane, )
    Re: Index on a NULL-value  (Tobias Brox, )
 Re: Index on a NULL-value  (Tobias Brox, )

Bruno Wolff III <> writes:
> Looked back at your first example and saw that you didn't use a partial
> index which is why you had to contort things to make it possible to
> use an indexed search.

FWIW, there is code in CVS tip that recognizes the connection between
an index on a boolean expression and a WHERE clause testing that
expression.  It's not quite perfect --- using Tobias' example I see

regression=#  explain select * from mock where b is NULL;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using b_is_null on mock  (cost=0.00..51.67 rows=10 width=8)
   Index Cond: ((b IS NULL) = true)
   Filter: (b IS NULL)
(3 rows)

so there's a useless filter condition still being generated.  But it
gets the job done as far as using the index, anyway.

> You want something like this:
> CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;

I think best practice for something like this is to make the partial
index's columns be something different from what the partial condition
tests.  Done as above, every actual index entry will be a null, so the
entry contents are just dead weight.  Instead do, say,

CREATE INDEX b_is_null ON mock(a) WHERE b IS NULL;

where a is chosen as a column that you frequently also test in
conjunction with "b IS NULL".  That is, the above index can efficiently
handle queries like

    ... WHERE a = 42 AND b IS NULL ...

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: "Praveen Raja"
Дата:
Сообщение: very large table
От: Dirk Lutzebäck
Дата:
Сообщение: SURVEY: who is running postgresql on 8 or more CPUs?