Re: Using index for IS NULL query

От: Andreas Kretschmer
Тема: Re: Using index for IS NULL query
Дата: ,
Msg-id: 20081111205049.GA15899@tux
(см: обсуждение, исходный текст)
Ответ на: Re: Using index for IS NULL query  (Tom Lane)
Список: pgsql-performance

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

Using index for IS NULL query  ("Andrus", )
 Re: Using index for IS NULL query  (Tom Lane, )
  Re: Using index for IS NULL query  (Andreas Kretschmer, )
  Re: Using index for IS NULL query  (Matthew Wakeling, )
 Re: Using index for IS NULL query  (Andreas Kretschmer, )
 Re: Using index for IS NULL query  (Tomas Vondra, )
  Re: Using index for IS NULL query  ("Vladimir Sitnikov", )
   Re: Using index for IS NULL query  ("Scott Marlowe", )
   Re: Using index for IS NULL query  (Tomas Vondra, )
  Index usage with sub select or inner joins  ("Julien Theulier", )
   Re: Index usage with sub select or inner joins  (Joshua Tolley, )
    Re: Index usage with sub select or outer joins  ("Julien Theulier", )

Tom Lane <> schrieb:

> "Andrus" <> writes:
> > Index is not used for
> >  is null
>
> > How to fix ?
>
> Update to something newer than 8.1 (specifically, you'll need 8.3).

Right. For my example in the other mail:

test=*# create index idx_foo on foo(i);
CREATE INDEX
test=*# explain analyse select * from foo where i is null;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=95.11..4780.49 rows=5000 width=8) (actual time=0.052..0.053 rows=1 loops=1)
   Recheck Cond: (i IS NULL)
   ->  Bitmap Index Scan on idx_foo  (cost=0.00..93.86 rows=5000 width=0) (actual time=0.047..0.047 rows=1 loops=1)
         Index Cond: (i IS NULL)
 Total runtime: 0.076 ms



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


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

От: Tom Lane
Дата:
Сообщение: Re: Oddity with view (now with test case)
От: Matthew Wakeling
Дата:
Сообщение: Re: Using index for IS NULL query