Indizierung

DuckDB hat zwei Arten von Indizes: Zonemaps und ART-Indizes.

Zonemaps

DuckDB erstellt automatisch Zonemaps (auch als Min-Max-Indizes bekannt) für die Spalten aller Allzweck-Datentypen. Operationen wie Prädikat-Pushdown in Scan-Operatoren und die Berechnung von Aggregationen nutzen Zonemaps. Ist ein Filterkriterium (wie WHERE column1 = 123) in Verwendung, kann DuckDB jede Zeilengruppe überspringen, deren Min-Max-Bereich den Filterwert nicht enthält (z. B. einen Block mit dem Min-Max-Bereich 1000 bis 2000 bei einem Vergleich auf = 123 oder < 400).

Der Einfluss der Sortierung auf Zonemaps

Je geordneter die Daten in einer Spalte sind, desto wertvoller sind die Zonemap-Indizes. Eine Spalte könnte im schlechtesten Fall in jeder Zeile eine Zufallszahl enthalten. Dann kann DuckDB wahrscheinlich keine Zeilengruppe überspringen. Wenn Sie bestimmte Spalten mit selektiven Filtern abfragen, ist es am besten, die Daten beim Einfügen nach diesen Spalten vorzusortieren. Selbst eine unvollständige Ordnung ist noch hilfreich. Der Idealfall geordneter Daten tritt häufig bei DATETIME-Spalten auf.

Mikrobenchmark: Der Einfluss der Sortierung

Als Beispiel wiederholen wir den Mikrobenchmark für Zeitstempel mit einer geordneten Zeitstempelspalte in aufsteigender Reihenfolge im Vergleich zu einer ungeordneten.

Spaltentyp	Sortiert	Speichergröße	Abfragezeit
DATETIME	ja	1.3 GB	0.6 s
DATETIME	nein	3.3 GB	0.9 s

Die Ergebnisse zeigen, dass allein das Beibehalten der Spaltenordnung eine bessere Kompression ermöglicht und eine 2.5× kleinere Speichergröße ergibt. Die Berechnung ist außerdem 1.5× schneller.

Geordnete Ganzzahlen

Eine weitere praktische Möglichkeit, Ordnung auszunutzen, ist der Typ INTEGER mit automatischer Inkrementierung statt UUID für Spalten, die mit selektiven Filtern abgefragt werden. Enthält eine Tabelle ungeordnete UUIDs, muss DuckDB viele Zeilengruppen scannen, um einen bestimmten UUID-Wert zu finden. Eine geordnete INTEGER-Spalte erlaubt es, alle Zeilengruppen außer denen mit dem gesuchten Wert zu überspringen.

ART-Indizes

DuckDB erlaubt die Definition von Adaptive-Radix-Tree-(ART-)Indizes auf zwei Arten. Erstens wird ein solcher Index implizit für Spalten mit den Constraints PRIMARY KEY, FOREIGN KEY und UNIQUE angelegt. Zweitens erzeugt die explizite Ausführung der Anweisung CREATE INDEX einen ART-Index auf der/den Zielspalte(n).

Die Vor- und Nachteile eines ART-Index auf einer Spalte sind:

1. ART-Indizes ermöglichen die Constraint-Prüfung bei Änderungen (Inserts, Updates und Deletes).
2. Änderungen an indizierten Tabellen sind langsamer als bei nicht indizierten Gegenstücken. Das liegt an der Indexpflege für diese Operationen.
3. In manchen Anwendungsfällen verbessern einspaltige ART-Indizes die Leistung hochselektiver Abfragen über die indizierte Spalte.

Ein ART-Index beeinflusst die Leistung von Join-, Aggregations- und Sortierabfragen nicht.

ART-Index-Scans

ART-Index-Scans durchsuchen einen einspaltigen ART-Index nach den angeforderten Daten, statt eine Tabelle sequenziell zu scannen. Das Sondieren kann die Leistung mancher Abfragen verbessern. DuckDB versucht, einen Index-Scan für Gleichheits- und IN(...)-Bedingungen zu verwenden. Außerdem werden dynamische Filter, z. B. aus Hash-Joins, in den Scan geschoben, sodass dynamische Index-Scans auf diesen Filtern möglich sind.

Indizes kommen für Index-Scans nur in Frage, wenn sie eine einzelne Spalte ohne Ausdrücke indizieren. Der folgende Index ist z. B. für Index-Scans geeignet:

CREATE INDEX idx ON tbl (col1);

Die folgenden beiden Indizes sind z. B. NICHT für Index-Scans geeignet:

CREATE INDEX idx_multi_column ON tbl (col1, col2);
CREATE INDEX idx_expr ON tbl (col1 + 1);

Der Standardschwellenwert für Index-Scans ist MAX(2048, 0.001 * table_cardinality). Sie können diesen Schwellenwert über index_scan_percentage und index_scan_max_count konfigurieren oder die Scans deaktivieren, indem Sie diese Werte auf null setzen. Im Zweifel prüfen Sie mit EXPLAIN ANALYZE, ob Ihr Abfrageplan den Index-Scan verwendet.

Indizes und Speicher

DuckDB registriert Indexspeicher über seinen Buffer-Manager. Diese Index-Buffer sind jedoch noch nicht buffer-verwaltet. Das bedeutet, DuckDB zerstört noch keine Index-Buffer, wenn Speicher verdrängt werden muss. Indizes können daher einen erheblichen Teil des verfügbaren DuckDB-Speichers belegen und die Leistung speicherintensiver Abfragen beeinträchtigen. Erneutes Anhängen (DETACH + ATTACH) der Datenbank mit den Indizes kann diesen Effekt mindern, da der Indexspeicher verzögert deserialisiert wird. Das Deaktivieren von Index-Scans und erneutes Anhängen nach Änderungen können den Einfluss von Indizes auf den verfügbaren DuckDB-Speicher weiter verringern.

Indizes und das Öffnen von Datenbanken

Indizes werden auf die Festplatte serialisiert und verzögert deserialisiert, d. h. beim erneuten Öffnen der Datenbank. Operationen, die den Index nutzen, laden nur die benötigten Teile des Index. Ein Index verursacht daher keine Verlangsamung beim Öffnen einer vorhandenen Datenbank.

Bestpractice Wir empfehlen die folgenden Richtlinien:

• Verwenden Sie Primärschlüssel, Fremdschlüssel oder Unique-Constraints nur, wenn sie zur Durchsetzung von Constraints auf Ihren Daten nötig sind.
• Definieren Sie keine expliziten Indizes, außer Sie haben hochselektive Abfragen und ausreichend Speicher.
• Wenn Sie einen ART-Index anlegen, tun Sie das nach dem Bulk-Laden der Daten in die Tabelle. Einen Index vor dem Laden anzulegen – explizit oder über Primär-/Fremdschlüssel – ist schädlich für die Ladeleistung.