PostgreSQL: Erstellen Sie einen index, um schnell zu unterscheiden, NULL von nicht-NULL-Werte

Betrachten Sie eine SQL-Abfrage mit der folgenden WHERE Prädikat:

...
WHERE name IS NOT NULL
...

Wo name ist ein Text-Feld in der PostgreSQL.

Keine andere Abfrage überprüfen Sie jede Art von Text-Eigenschaft mit diesem Wert, nur ob es NULL oder nicht. Daher eine vollständige btree-index scheint wie ein overkill, auch wenn es unterstützt diese Unterscheidung:

Auch, eine IST NULL oder IST NICHT NULL-Bedingung, die auf einer index-Spalte kann verwendet werden, mit einer B-tree-index.

Was ist die richtige PostgreSQL-index schnell zu unterscheiden NULLs aus nicht-NULLs?

Sie können fügen Sie Prädikat der create index zumindest Minimierung Ihrer Größe.
Wie Sie haben keine andere Wahl, als btree, gist, gin und hash, die entmutigt, ich sehe nicht ein anderer Weg möglich.
Sie können create index i on t (coalesce('NULL',col)); tatsächlich index NULL und Trennung einer null-Werte aus anderen Nullen
o_O? NULL indiziert ist. Wo kommst du auf die Idee, dass es gar nicht?
ja es ist indiziert, aber alle null-Werte Verschieden sind, und ich glaube, Adam will, um zu sehen, null-Werte als gleich. Adam?..
Der b-Baum für NULL enthält eine Zeile Zeiger auf jede Zeile mit NULL in diesem Bereich. Während Sie ungleich, Sie alle entsprechen, das Prädikat IS NULL. Dein Vorschlag keinen Sinn macht, und nicht nur das, Pg könnte nur dann tatsächlich den index verwenden, wenn die Abfrage auch aus dem gleichen Ausdruck.
Stimmt - ich will nur, um eine Unterscheidung zwischen Null und nicht-Null-Werte, I don ' T care über den tatsächlichen Wert.

InformationsquelleAutor Adam Matan | 2015-08-12

24

Bin ich Dolmetschen Sie behaupten, dass es "overkill" in zweierlei Hinsicht: in Bezug auf die Komplexität (Verwendung eines B-Baum anstatt einfach nur eine Liste) und Raum - /Leistung.

Für Komplexität, es ist nicht übertrieben. Ein B-Tree-index ist vorzuziehen, da löscht aus, es wird schneller sein als eine Art "ungeordnete" index (aus Mangel an eines besseren Begriffs). (Eine unsortierte index würde einen full index scan einfach zu löschen.) Angesichts der Tatsache, dass, alle Gewinne aus einem unsortierten index wäre in der Regel überwiegen die Nachteile, so dass die Entwicklung Aufwand nicht gerechtfertigt.

Für Raum und Leistung, wird aber, wenn Sie möchten, eine hoch selektive index für Effizienz, Sie können auch eine WHERE - Klausel, die auf einen index wie angegeben in der Handbuch:
```
CREATE INDEX ON my_table (name) WHERE name IS NOT NULL;
```
Beachten Sie, dass Sie nur sehen, profitiert von diesem index, wenn Sie es zulassen können, PostgreSQL ignorieren großen Menge der Zeilen, die beim ausführen der Abfrage. E. g., wenn 99% der Zeilen haben name IS NOT NULL, der index nicht kaufen Sie nichts, nur, dass ein full table scan geschehen; in der Tat, es wäre weniger effizient (wie @CraigRinger Noten), weil es erfordern würde, die extra-disk liest. Wenn jedoch nur 1% der Zeilen name IS NOT NULL, dann stellt dies eine große ersparnis wie PostgreSQL ignorieren können, die meisten der in der Tabelle für die Abfrage. Wenn deine Tabelle sehr groß ist, auch den Wegfall von 50% der Zeilen könnte es Wert sein. Dies ist ein tuning-problem, und ob der index ist wertvoller wird, hängt stark von der Größe und Verteilung der Daten.

Darüber hinaus gibt es sehr wenig Verstärkung in Form von Raum, wenn müssen Sie noch weitere Indizes für die name IS NULL Zeilen. Sehen Craig Ringer-Antwort für details.
- Gut erklärt. In der Tat, mit dem index für etwas, das würde passen 99% der Zeilen wäre äußerst ineffizient, viel langsamer als seqscan.
InformationsquelleAutor jpmc26

Könnten Sie einen Ausdruck verwenden, der index, aber man sollte Sie nicht. Halten Sie es einfach, und mit einem einfachen b-Baum.

Einem Ausdruck ein index kann erstellt werden, auf colname IS NOT NULL:

test=> CREATE TABLE blah(name text);
CREATE TABLE
test=> CREATE INDEX name_notnull ON blah((name IS NOT NULL));
CREATE INDEX
test=> INSERT INTO blah(name) VALUES ('a'),('b'),(NULL);
INSERT 0 3
test=> SET enable_seqscan = off;
SET
craig=> SELECT * FROM blah WHERE name IS NOT NULL;
 name 
------
 a
 b
(2 rows)

test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Bitmap Heap Scan on blah  (cost=9.39..25.94 rows=1303 width=32)
   Filter: (name IS NOT NULL)
   ->  Bitmap Index Scan on name_notnull  (cost=0.00..9.06 rows=655 width=0)
         Index Cond: ((name IS NOT NULL) = true)
(4 rows)

test=> SET enable_bitmapscan = off;
SET
test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using name_notnull on blah  (cost=0.15..55.62 rows=1303 width=32)
   Index Cond: ((name IS NOT NULL) = true)
   Filter: (name IS NOT NULL)
(3 rows)

... aber Pg nicht bemerkt, dass es auch verwendbar für IS NULL:

test=> EXPLAIN SELECT * FROM blah WHERE name IS NULL;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Seq Scan on blah  (cost=10000000000.00..10000000023.10 rows=7 width=32)
   Filter: (name IS NULL)
(2 rows)

und auch verwandelt NOT (name IS NOT NULL) in name IS NULL, die in der Regel, was Sie wollen.

test=> EXPLAIN SELECT * FROM blah WHERE NOT (name IS NOT NULL);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Seq Scan on blah  (cost=10000000000.00..10000000023.10 rows=7 width=32)
   Filter: (name IS NULL)
(2 rows)

also du bist eigentlich besser dran mit zwei getrennten Ausdruck von Indizes, auf die null und die eins auf null gesetzt.

test=> DROP INDEX name_notnull ;
DROP INDEX
test=> CREATE INDEX name_notnull ON blah((name IS NOT NULL)) WHERE (name IS NOT NULL);
CREATE INDEX
test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Scan using name_notnull on blah  (cost=0.13..8.14 rows=3 width=32)
   Index Cond: ((name IS NOT NULL) = true)
(2 rows)

test=> CREATE INDEX name_null ON blah((name IS NULL)) WHERE (name IS NULL);
CREATE INDEX
craig=> EXPLAIN SELECT * FROM blah WHERE name IS NULL;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using name_null on blah  (cost=0.12..8.14 rows=1 width=32)
   Index Cond: ((name IS NULL) = true)
(2 rows)

Dies ist ziemlich grausam, wenn. Für die meisten sinnvoll nutzt, ich würde nur mit einem einfachen b-tree-index. Die index-Größe Verbesserung ist nicht allzu spannend, zumindest für kleine-ish-Eingänge, wie den dummy habe ich mit ein paar md5-Werte:

test=> SELECT pg_size_pretty(pg_relation_size('blah'));
 pg_size_pretty 
----------------
 9416 kB
(1 row)

test=> SELECT pg_size_pretty(pg_relation_size('blah_name'));
 pg_size_pretty 
----------------
 7984 kB
(1 row)

test=> SELECT pg_size_pretty(pg_relation_size('name_notnull'));
 pg_size_pretty 
----------------
 2208 kB
(1 row)

test=> SELECT pg_size_pretty(pg_relation_size('name_null'));
 pg_size_pretty 
----------------
 2208 kB
(1 row)

InformationsquelleAutor Craig Ringer

3

Können Sie einen Ausdruck wie (Titel IST NULL) als die indizierte Spalte. Damit dies funktioniert, wie erwartet:
```
CREATE INDEX index_articles_on_title_null ON articles ( (title IS NULL) );
SELECT * FROM articles WHERE (title IS NULL)='t';
```
Dies hat den großen Vorteil gegenüber den mit einem Prädikat, dass in diesem Fall der gespeicherte Wert in dem index ist nur eine ja/Nein boolean und nicht die gesamte Spalte Wert. So vor allem, wenn Sie Ihr NULL-Spalte überprüft tendenziell enthalten große Werte (wie bei einem Titel-Feld text hier), dann ist diese Art der Indizierung ist viel mehr Platz-effizient als die Verwendung einer ausgesagt index.
- Sie brauchen nicht die = 't'. Mit WHERE title IS NULL gut funktionieren wird
InformationsquelleAutor fxtentacle

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.