Wie kann ich die Kosten eines Datenbankindexes messen?

Ich werde tatsächlich mit Hexist nicht einverstanden sein. Der Planer von PostgreSQL ist ziemlich gut, und er unterstützt guten sequentiellen Zugriff auf Tabellendateien basierend auf physischen Auftragsscans, so dass Indizes nicht unbedingt helfen werden. Zusätzlich gibt es viele Fälle, in denen der Planer einen Index auswählen muss. Außerdem erstellen Sie bereits Primärschlüssel für eindeutige Integritätsbedingungen und Primärschlüssel.

Ich denke, eine der guten Standardpositionen bei PostgreSQL (MySQL ist übrigens völlig anders!) besteht darin, zu warten, bis Sie einen Index benötigen, um einen Index hinzuzufügen und dann nur die Indizes hinzuzufügen, die Sie am dringendsten benötigen. Dies ist jedoch nur ein Anfangspunkt und es wird entweder ein Mangel an allgemeinem Mangel an Erfahrung bei der Suche nach Abfrageplänen oder ein mangelndes Verständnis dafür, wohin die Anwendung wahrscheinlich gehen wird, angenommen. Erfahrung in diesen Bereichen zählt.

Im Allgemeinen ist es eine gute Idee, wenn Sie Tabellen mit mehr als 10 Seiten haben (das sind 40 KB Daten und Header). Diese können als eindeutig benötigt angesehen werden. Kleine Nachschlagetabellen, die eine Seite umfassen, sollten nie nicht eindeutige Indizes aufweisen, da diese Indizes niemals für Auswahlvorgänge verwendet werden (kein Abfrageplan schlägt einen sequenziellen Scan über eine einzelne Seite hinweg).

Darüber hinaus müssen Sie sich auch die Datenverteilung ansehen. Das Indizieren von booleschen Spalten ist normalerweise eine schlechte Idee und es gibt bessere Möglichkeiten, Dinge im Zusammenhang mit booleschen Suchen zu indizieren (Teilindizes sind ein gutes Beispiel). Ähnlich kann die Indizierung der häufig verwendeten Funktionsausgabe manchmal als eine gute Idee erscheinen, aber das ist nicht immer der Fall. Überlegen Sie:

Das wird nicht viel tun. Ein Index für Transdate könnte jedoch nützlich sein, wenn er mit einem spärlichen Index-Scan über einen rekursiven CTE gepaart wird.

Sobald die grundlegenden Indizes vorhanden sind, stellt sich die Frage, welche anderen Indizes hinzugefügt werden müssen. Dies ist oft besser für die spätere Verwendung der Fallüberprüfung als ursprünglich geplant. Es ist nicht ungewöhnlich, dass Leute feststellen, dass die Leistung von weniger Indizes für PostgreSQL profitiert.

Eine weitere wichtige Sache, die Sie beachten sollten, ist die Art der Indizes, die Sie erstellen, und diese sind oft anwendungsspezifisch. Ein B-Tree-Index für einen Array-Datensatz könnte beispielsweise sinnvoll sein, wenn die Ordinalität für die Domain wichtig ist. Wenn Sie häufig nach anfänglichen Elementen suchen, aber die Ordinalität nicht wichtig ist, würde ich einen GIN-Index empfehlen, da dies ein btree ist tue sehr wenig gut (natürlich ist das eine Atomicity Red Flag, aber manchmal macht das Sinn in Pg). Selbst wenn die Ordinalität wichtig ist, benötigen Sie manchmal GIN-Indizes, da Sie in der Lage sein müssen, kommutitive Scans durchzuführen, als ob die Ordinalitat nicht ginge. Dies ist der Fall, wenn Sie ip4r verwenden, um beispielsweise cidr-Blöcke zu speichern und eine EXCLUDE-Einschränkung zu verwenden, um sicherzustellen, dass kein Block einen anderen Block enthält (der eigentliche Scan erfordert einen Overlap-Operator anstelle eines containers, da Sie nicht wissen, welche Seite des Betreiber der Verstoß wird auf) gefunden werden.

Auch dies ist etwas datenbankspezifisch. Auf MySQL wären Hexists Empfehlungen zum Beispiel richtig. Auf PostgreSQL ist es jedoch gut, auf Probleme zu achten.

Was das Messen betrifft, ist das beste Werkzeug EXPLAIN ANALYZE

Wenn Sie nicht eine Log- oder Archiv-Tabelle haben, die Sie nicht sehr oft auswählen (oder wenn es eine Weile dauert), sollten Sie alles, was Ihre select / update / deelete-Anweisungen verwenden, indizieren in einer where-Klausel.

Dies ist jedoch nicht immer so einfach wie es scheint, denn nur weil eine Spalte in einer WHERE-Klausel verwendet wird und indiziert wird, heißt das nicht, dass die SQL-Engine den Index verwenden kann. Mit den Funktionen EXPLAIN und EXPLAIN ANALYZE von postgresql können Sie untersuchen, welche Indizes in selects verwendet wurden, und Ihnen helfen, herauszufinden, ob Ihnen ein Index für eine Spalte hilft.

Dies ist im Allgemeinen der Fall, weil ohne einen Index die ausgewählte Geschwindigkeit von einer O (log n) -Suchoperation auf O (n) sinkt, während sich die Einfügegeschwindigkeit nur von cO (log n) nach dO (log n) verbessert. wobei d normalerweise kleiner ist als c, dh Sie können Ihre Einsätze ein wenig beschleunigen, indem Sie keinen Index haben, aber Sie werden Ihre ausgewählte Geschwindigkeit abbrechen, wenn sie nicht indexiert sind, so dass es sich fast immer lohnt, einen Index zu haben auf Ihre Daten, wenn Sie dagegen auswählen.

Wenn Sie nun eine kleine Tabelle haben, in der Sie viele Einfügungen und Aktualisierungen vornehmen und häufig alle Einträge entfernen und nur einige Auswahlen regelmäßig ausführen, könnte es schneller sein, keine Indizes zu haben. Dies wäre jedoch ein ziemlich spezielles Fall-Szenario. Sie müssten also ein Benchmarking durchführen und entscheiden, ob es in Ihrem speziellen Fall sinnvoll ist.

Nette Frage. Ich möchte ein bisschen mehr hinzufügen, was @hexist bereits erwähnt hat und zu den Informationen, die von @ ypercubes Link bereitgestellt werden.

Vom Entwurf her weiß die Datenbank nicht, in welchem ​​Teil der Tabelle sie Daten findet, die den bereitgestellten Prädikaten . Daher führt die Datenbank einen vollständigen oder sequenziellen Scan aller Tabellendaten durch und filtert die benötigten Zeilen.

Index ist eine spezielle Datenstruktur, die für eine gegebene key genau angeben kann, in welchen Zeilen der Tabelle solche Werte gefunden werden. Der Hauptunterschied beim Index ist:

1. es gibt Kosten für den Index-Scan selbst, d. h. DB muss zuerst einen Wert im Index finden;
2. es gibt zusätzliche Kosten für das Lesen bestimmter Daten aus der Tabelle selbst.

Das Arbeiten mit dem Index führt zu einem zufälligen E / A-Muster, verglichen mit einem sequentiellen E / A-Muster, das im vollständigen Scan verwendet wird. Sie können nach den Vergleichszahlen des zufälligen und sequentiellen Festplattenzugriffs googlen, aber es kann bis zu einer Größenordnung variieren (zufällig ist es natürlich langsamer).

Dennoch ist es klar, dass in einigen Fällen der Indexzugriff billiger ist und in anderen Fällen sollte der vollständige Scan bevorzugt werden. Dies hängt davon ab, wie viele Zeilen (von allen) vom angegebenen Prädikat oder seiner Selektivität zurückgegeben werden:

1. Wenn das Prädikat eine relativ kleine Anzahl von Zeilen zurückgibt, z. B. weniger als 10% des Gesamtwerts, dann erscheint es sinnvoll, diese direkt über Index auszuwählen. Dies ist ein typischer Fall für primäre / eindeutige Schlüssel oder Abfragen wie: I need address information for customer with internal number = XXX ;
2. Wenn Prädikat keinen großen Einfluss auf die Selektivität hat, d. h. wenn 30% (oder mehr) Zeilen zurückgegeben werden, ist es günstiger, einen Full-Scan durchzuführen, da der sequentielle Festplattenzugriff zufälliger wird und die Daten schneller geliefert werden. Alle Berichte, die große Bereiche (wie einen Monat oder alle Kunden) abdecken, fallen hier;
3. Wenn es notwendig ist, eine geordnete Liste von Werten zu erhalten, und es gibt einen Index, dann ist der Index-Scan die schnellste Option. Dies ist ein spezieller Fall von # 2, wenn Sie Berichtdaten benötigen, die nach einer Spalte sortiert sind;
4. Wenn die Anzahl der distinct -Werte in der Spalte im Vergleich zur Gesamtzahl der Werte relativ klein ist, ist Index eine gute Wahl. Dies ist ein Fall, der Loose Index Scan genannt wird, und typische Abfragen lauten wie folgt: I need 20 most recent purchases for each of the top 5 categories by number of goods .

Wie entscheidet DB, was zu tun ist, Index oder vollständiger Scan? Dies ist eine Laufzeitentscheidung und basiert auf den Statistiken, also stellen Sie sicher, dass diese auf dem neuesten Stand gehalten werden. Tatsächlich haben die oben angegebenen Zahlen keinen realen Wert, Sie müssen jede Anfrage unabhängig voneinander bewerten.

All dies ist eine sehr grobe Beschreibung dessen, was passiert. Ich würde sehr empfehlen, Wie PostgreSQL Planner Statistiken verwendet , das Beste was ich zu dem Thema gesehen habe.