Indizes

Indextypen

DuckDB hat zwei eingebaute Indextypen. Indizes können auch über Erweiterungen definiert werden.

Min-Max-Index (Zonemap)

Ein Min-Max-Index (auch Zonemap oder Block-Range-Index genannt) wird für Spalten aller allgemeinen Datentypen automatisch erzeugt.

Adaptive Radix Tree (ART)

Ein Adaptive Radix Tree (ART) dient vor allem der Sicherung von Primärschlüsselbedingungen und der Beschleunigung von Punktabfragen und sehr hochselektiven Abfragen (d. h. < 0,1 %). ART-Indizes können manuell mit der Klausel CREATE INDEX erzeugt werden und entstehen automatisch für Spalten mit einer UNIQUE- oder PRIMARY KEY-Bedingung.> Warnung ART-Indizes müssen derzeit während der Indexerzeugung in den Speicher passen. Legen Sie keine ART-Indizes an, wenn der Index bei der Erzeugung nicht in den Speicher passt.

Von Erweiterungen definierte Indizes

DuckDB unterstützt R-Bäume für räumliche Indizierung über die Erweiterung spatial.

Persistenz

Sowohl Min-Max-Indizes als auch ART-Indizes werden auf der Festplatte persistiert.

Anweisungen CREATE INDEX und DROP INDEX

Um einen ART-Index anzulegen, verwenden Sie die Anweisung CREATE INDEX. Um einen ART-Index zu entfernen, verwenden Sie die Anweisung DROP INDEX.

Einschränkungen von ART-Indizes

ART-Indizes erzeugen eine zweite Kopie der Daten an einem zweiten Ort. Die Pflege dieser zweiten Kopie erschwert die Verarbeitung. Daher gelten derzeit bestimmte Einschränkungen, wenn Daten geändert werden, die auch in sekundären Indizes gespeichert sind.

Wie erwartet wirken sich Indizes stark auf die Leistung aus: Laden und Aktualisierungen werden langsamer, bestimmte Abfragen schneller. Details finden Sie im Performance Guide.

Bedingungsprüfung in UPDATE-Anweisungen

UPDATE-Anweisungen auf indizierten Spalten und auf Spalten, die nicht an Ort und Stelle aktualisiert werden können, werden in ein DELETE der ursprünglichen Zeile gefolgt von einem INSERT der aktualisierten Zeile umgeschrieben. Dieses Umschreiben hat Leistungsfolgen, besonders bei breiten Tabellen, weil ganze Zeilen statt nur der betroffenen Spalten neu geschrieben werden.

Zusätzlich führt das zur folgenden Einschränkung der Bedingungsprüfung bei UPDATE-Anweisungen. Dieselbe Einschränkung gibt es in anderen DBMS, etwa PostgreSQL.

Im Beispiel unten übersteigt die Zeilenzahl die Standard-Vektorgröße von DuckDB, die standardmäßig 2048 beträgt. Die UPDATE-Anweisung wird in ein DELETE gefolgt von einem INSERT umgeschrieben. Dieses Umschreiben geschieht pro Datenchunk (2048 Zeilen), der durch die Verarbeitungspipeline von DuckDB läuft. Wenn i = 2047 auf i = 2048 aktualisiert wird, wissen wir noch nicht, dass 2048 zu 2049 wird usw. Denn diesen Chunk haben wir noch nicht gesehen. Daher wird eine Bedingungsverletzung ausgelöst.

CREATE TABLE my_table (i INTEGER PRIMARY KEY);
INSERT INTO my_table SELECT range FROM range(3_000);
UPDATE my_table SET i = i + 1;

Constraint Error:
Duplicate key "i: 2048" violates primary key constraint.

Ein Workaround ist, das UPDATE in ein DELETE ... RETURNING ... gefolgt von einem INSERT aufzuteilen, mit etwas zusätzlicher Logik, um das Ergebnis des DELETE (vorübergehend) zu speichern. Alle Anweisungen sollten in einer Transaktion über BEGIN und schließlich COMMIT laufen.

So könnte das im Kommandozeilen-Client aussehen.

CREATE TABLE my_table (i INTEGER PRIMARY KEY);
INSERT INTO my_table SELECT range FROM range(3_000);

BEGIN;
CREATE TEMP TABLE tmp AS SELECT i FROM my_table;
DELETE FROM my_table;
INSERT INTO my_table SELECT i FROM tmp;
DROP TABLE tmp;
COMMIT;

In anderen Clients können Sie das Ergebnis von DELETE ... RETURNING ... holen. Anschließend können Sie dieses Ergebnis in einer folgenden INSERT ...-Anweisung verwenden oder ggf. den Appender von DuckDB nutzen (falls im Client verfügbar).

Zu frühe Bedingungsprüfung bei Fremdschlüsseln

Diese Einschränkung tritt auf, wenn die folgenden Bedingungen zutreffen:

• Eine Tabelle hat eine FOREIGN KEY-Bedingung.
• Es gibt ein UPDATE auf einer zusammengesetzten Payload-Spalte (z. B. einer LIST oder einem STRUCT) der zugehörigen PRIMARY KEY-Tabelle, das DuckDB in ein DELETE gefolgt von einem INSERT umschreibt.
• Die zu löschende Zeile existiert in der Fremdschlüssel-Tabelle.

Dann erhalten Sie eine unerwartete Bedingungsverletzung:

CREATE TABLE pk_table (id INTEGER PRIMARY KEY, payload VARCHAR[]);
INSERT INTO pk_table VALUES (1, ['hello']);
CREATE TABLE fk_table (id INTEGER REFERENCES pk_table(id));
INSERT INTO fk_table VALUES (1);
UPDATE pk_table SET payload = ['world'] WHERE id = 1;

Constraint Error:
Violates foreign key constraint because key "id: 1" is still referenced by a foreign key in a different table. If this is an unexpected constraint violation, please refer to our foreign key limitations in the documentation

Der Grund ist, dass DuckDB noch kein „Vorausschauen“ unterstützt. Während des INSERT weiß es nicht, dass es den Fremdschlüsselwert im Zuge des UPDATE-Umschreibens wieder einfügen wird.