Zusammengesetzte und partielle Indizes

In früheren Lektionen haben wir die Verwendung von Indizes für lineare DBMS-Abfragen (WHERE country = 'Italy') behandelt. Was ist, wenn wir an zwei kombinierten Fronten suchen?

In einem echten E-Commerce möchten wir beispielsweise möglicherweise Bestellungen finden, die zu einer bestimmten Kunden-ID gehören UND in einen bestimmten Zeitraum fallen: WHERE customer_id = 90 AND placed_at >= '2025-01-01'.

Wenn Sie nur einen Basisindex für customer_id haben, findet Postgres mithilfe des Binärbaums in kürzester Zeit die 50 Bestellungen des Kunden, muss dann aber alle 50 Bestellungen manuell und nacheinander durchlaufen und dabei die Daten träge nach Augenmaß filtern.

Zusammengesetzte Indizes

Wir können das DBMS anweisen, die „analytischen Wörterbuch“-Dateien nicht nach einer einzelnen Schlüsseldimension, sondern nach mehreren zu ordnen.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);

Ein „zusammengesetzter“ Index behandelt die Reihenfolge seiner Parameter als sehr wichtig: Hier gruppieren und kategorisieren B-Tree-Knoten customer_id streng in einem Block (dem Block mit der höchsten Unterscheidungskraft für die Trennung); In ihnen lebt der verzweigte Teilbaum mit den verschiedenen placed_at-Zeitwerten in perfekt sortierter Reihenfolge.

Diese Struktur vernichtet WHERE-Klauseln mit mehreren verschachtelten Prädikaten brutal.

Teilindizes (gefilterte Indizes)

Jeder Index kostet Speicherplatz und CPU für Benutzereingabestreams. Was wäre, wenn der E-Commerce das ständige Sortieren und Abrufen eines Filters erfordern würde, jedoch nur für eine Minderheit der Datensätze? (Zum Beispiel: Schnelles Abfragen der Prüfung auf ungelesene Benachrichtigungen.) Das Erstellen eines „is_read“-Index über alle 10.000.000 Benachrichtigungen ist unglaublich mühsam, wenn „Ungelesen“ nur fünf davon darstellt.

CREATE INDEX idx_unread_notifs ON notifications(user_id)
  WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"

Die Datenbank erstellt einen blitzschnellen B-Baum, der Schnellzugriffsschlüssel auf die Datenbank NUR speichert, wenn der Datensatz ungelesen ist! Die Festplatte erhält einen Index mit 5 Zeilen (3 Bytes) statt einem mit 10 Millionen. Fast SELECTs profitieren in vollem Umfang:

SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;

Du bist dran

Erkunden bedingter (partieller) Indizes