Nulls nicht indizierbar, Index trotzdem riesig

Hallo,

ich habe eine Spalte, die sehr viel NULLs enthält. Diese ist ein Foreign Key. Aus diesem Grund habe ich ein Index darauf erstellt. Im Handbuch steht (soweit ich mich erinnern kann), dass NULL nicht indizierbar ist. Der Index ist von der Größe her 3 GB groß. Wenn ich ein Index erstelle mit der Einschränkung WHERE is NOT NULL, dann ist er nur noch 10 MB groß. Ist das nicht etwas unsinnig Werte die nicht indizierbar sind trotzdem soviel Speicherplatz im Index belegen zu lassen?

Demnach wäre es doch logischer jeden Index mit der NOT NULL Einschränkung zu erstellen, oder gibt es einen Anwendungsfall, den ich hier nicht bedenke?

David

Wo steht das?

test=# create table index_not_null_test (id serial, data text);
NOTICE:  CREATE TABLE will create implicit sequence "index_not_null_test_id_seq" for serial column "index_not_null_test.id"
CREATE TABLE
test=# create index index_not_null_test_data_idx on index_not_null_test(data);
CREATE INDEX
test=# insert into index_not_null_test (data) values ('abc'), ('def'), ('ghi');
INSERT 0 3
test=# set enable_seqscan = off;
SET
test=# select * from index_not_null_test where data is null;
 id | data
----+------
(0 rows)

test=# explain select * from index_not_null_test where data is null;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on index_not_null_test  (cost=4.30..13.75 rows=6 width=36)
   Recheck Cond: (data IS NULL)
   ->  Bitmap Index Scan on index_not_null_test_data_idx  (cost=0.00..4.30 rows=6 width=0)
         Index Cond: (data IS NULL)
(4 rows)

test=#

Doch, das klappt schon:

test=*# create table nullen (id serial, wert int);
NOTICE:  CREATE TABLE will create implicit sequence "nullen_id_seq" for serial column "nullen.id"
CREATE TABLE                                                                                     
Zeit: 9,726 ms                                                                                   
test=*# insert into nullen (wert) select (null) from generate_Series(1,100000) s;
INSERT 0 100000                                                                  
Zeit: 385,025 ms                                                                 
test=*# insert into nullen (wert) select (s) from generate_Series(1,100)s;
INSERT 0 100                                                              
Zeit: 0,670 ms                                                            
test=*# insert into nullen (wert) select (null) from generate_Series(1,100000) s;
INSERT 0 100000                                                                  
Zeit: 398,836 ms                                                                 
test=*# create index idx_wert on nullen(wert);
CREATE INDEX                                  
Zeit: 243,950 ms                              
test=*# analyze nullen;
ANALYZE                
Zeit: 16,744 ms        
test=*# explain select id from nullen where wert = 50;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using idx_wert on nullen  (cost=0.00..8.29 rows=1 width=4) 
   Index Cond: (wert = 50)                                             
(2 Zeilen)

Index wird genutzt.

Selbe Tabelle:

test=*# explain select id from nullen where wert = NULL;
                          QUERY PLAN                           
---------------------------------------------------------------
 Result  (cost=0.00..2786.00 rows=1 width=4)                   
   One-Time Filter: NULL::boolean                              
   ->  Seq Scan on nullen  (cost=0.00..2786.00 rows=1 width=4) 
(3 Zeilen)                                                     

Zeit: 0,282 ms
test=*# explain select id from nullen where wert is null;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on nullen  (cost=0.00..2786.00 rows=200033 width=4) 
   Filter: (wert IS NULL)                                     
(2 Zeilen)

Vergleiche mit NULL gehen nicht, die erste Query bringt real 0 Ergebnisse, Vergleich mit is NULL geht. In beiden Fällen erfolgt ein Seq- Scan, da die Tabelle ja fast nur als NULL besteht, der Index ist also nicht ausreichend selektiv.

test=# delete from nullen;
DELETE 200100
Zeit: 405,615 ms
test=*# insert into nullen (wert) select (s) from generate_Series(1,100000) s;
INSERT 0 100000
Zeit: 942,539 ms
test=*# insert into nullen (wert) select (NULL) from generate_Series(1,100)s;
INSERT 0 100
Zeit: 0,966 ms
test=*# insert into nullen (wert) select (s) from generate_Series(1,100000) s;
INSERT 0 100000
Zeit: 894,167 ms
test=*# analyze nullen;
ANALYZE
Zeit: 26,814 ms
test=*# explain select id from nullen where wert = 50;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_wert on nullen  (cost=0.00..11.27 rows=2 width=4)
   Index Cond: (wert = 50)
(2 Zeilen)

Zeit: 0,557 ms
test=*# explain select id from nullen where wert is NULL;
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_wert on nullen  (cost=0.00..8.30 rows=1 width=4)
   Index Cond: (wert IS NULL)
(2 Zeilen)

Zeit: 0,347 ms

Nun erfolgt auch bei is NULL ein Index-Scan, da nur sehr wenige Zeilen NULL enthalten ist das ein ausreichend selektiver Index.

Damit ist auch Dein Problem erklärt: Dein Index ist sehr groß, nützt aber nix, da bei Suche mit is NULL nicht selektiv. PG besitzt, wie von Dir schon genutzt, partielle Indexe. Damit fährst Du in solchen Situationen halt besser.

Ich glaub ich hab was aus der Dokumentation von PostgreSQL falsch verstanden:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: CREATE INDEX

Dort steht:

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.

Danke, ich glaub jetzt hab ich es verstanden.

David