Index on a NULL-value
От | Tobias Brox |
---|---|
Тема | Index on a NULL-value |
Дата | |
Msg-id | 20050531030207.GD26100@tobias.exoweb.net обсуждение исходный текст |
Ответы |
Re: Index on a NULL-value
Re: Index on a NULL-value Re: Index on a NULL-value |
Список | pgsql-performance |
I read in the manual today: Indexes are not used for IS NULL clauses by default. The best way to use indexes in such cases is to create a partial index using an IS NULL predicate. This is from the documentation for PostgreSQL 8. I did not find anything equivalent in the 7.4.8-documentation. I wasn't aware of this until it became an issue :-) Well, so I follow the tip but in vain. Reduced and reproduced like this in PostgreSQL 7.4.7: test=# create table mock(a int, b int); CREATE TABLE test=# create index b_is_null on mock((b IS NULL)); CREATE INDEX test=# insert into mock values (10,20); INSERT 70385040 1 test=# insert into mock values (20,30); INSERT 70385041 1 test=# insert into mock values (30, NULL); INSERT 70385042 1 test=# set enable_seqscan=off; SET test=# explain select * from mock where b is NULL; QUERY PLAN -------------------------------------------------------------------- Seq Scan on mock (cost=100000000.00..100000020.00 rows=6 width=8) Filter: (b IS NULL) (2 rows) vacuum analyze also didn't help to recognize the index ;-) Any tips? Rewrite the application to not use NULL-values? Hide under bedclothes and hope the problem goes away? Install more memory in the server? :-) -- Tobias Brox, Beijing
В списке pgsql-performance по дате отправления: