Geekly articles weekly

Indizes in PostgreSQL - 1

Einführung


Diese Artikelserie befasst sich hauptsächlich mit Indizes in PostgreSQL.

Jedes Thema kann aus verschiedenen Perspektiven betrachtet werden. Wir werden Dinge diskutieren, die einen Anwendungsentwickler interessieren sollten, der DBMS verwendet: Welche Indizes sind verfügbar, warum gibt es so viele verschiedene Arten von Indizes und wie können sie verwendet werden, um Abfragen zu beschleunigen. Das Thema kann wahrscheinlich in weniger Worten behandelt werden, aber im Geheimen hoffen wir auf einen neugierigen Entwickler, der sich auch für Details der Interna interessiert, zumal das Verständnis solcher Details es Ihnen ermöglicht, nicht nur auf das Urteil anderer zu verzichten, sondern auch Schlussfolgerungen zu ziehen von dir.

Die Entwicklung neuer Arten von Indizes liegt außerhalb des Anwendungsbereichs. Dies erfordert Kenntnisse der Programmiersprache C und bezieht sich eher auf das Fachwissen eines Systemprogrammierers als eines Anwendungsentwicklers. Aus dem gleichen Grund werden wir Programmierschnittstellen fast nicht diskutieren, sondern uns nur auf das konzentrieren, was für die Arbeit mit gebrauchsfertigen Indizes wichtig ist.

In diesem Artikel werden wir die Verteilung der Verantwortlichkeiten zwischen der allgemeinen Indexierungs-Engine in Bezug auf den DBMS-Kern und den einzelnen Indexzugriffsmethoden erläutern, die PostgreSQL als Erweiterungen hinzufügen kann. Im nächsten Artikel werden wir die Schnittstelle der Zugriffsmethode und kritische Konzepte wie Klassen und Operatorfamilien diskutieren. Nach dieser langen, aber notwendigen Einführung werden wir Details der Struktur und Anwendung verschiedener Arten von Indizes betrachten: Hash , B-Baum , GiST , SP-GiST , GIN und RUM , BRIN und Bloom .

Bevor wir beginnen, möchte ich Elena Indrupskaya für die Übersetzung der Artikel ins Englische danken.
Die Dinge haben sich seit der ursprünglichen Veröffentlichung ein wenig geändert. Meine Kommentare zum aktuellen Stand der Dinge sind so angegeben.

Indizes


In PostgreSQL sind Indizes spezielle Datenbankobjekte, die hauptsächlich dazu dienen, den Datenzugriff zu beschleunigen. Es handelt sich um Hilfsstrukturen: Jeder Index kann gelöscht und aus den Informationen in der Tabelle wiederhergestellt werden. Es kann vorkommen, dass Sie hören, dass ein DBMS nur langsam ohne Indizes arbeiten kann. Dies ist jedoch nicht der Fall, da Indizes auch dazu dienen, einige Integritätsbeschränkungen durchzusetzen.

Derzeit sind sechs verschiedene Arten von Indizes in PostgreSQL 9.6 integriert, und ein weiterer Index ist als Erweiterung verfügbar - dank erheblicher Änderungen in Version 9.6. Erwarten Sie also in naher Zukunft neue Arten von Indizes.

Trotz aller Unterschiede zwischen den Indextypen (auch als Zugriffsmethoden bezeichnet) ordnet jeder von ihnen schließlich einen Schlüssel (z. B. den Wert der indizierten Spalte) Tabellenzeilen zu, die diesen Schlüssel enthalten. Jede Zeile wird durch TID (Tupel-ID) identifiziert, die aus der Anzahl der Blöcke in der Datei und der Position der Zeile innerhalb des Blocks besteht. Mit dem bekannten Schlüssel oder einigen Informationen darüber können wir jedoch schnell die Zeilen lesen, die die Informationen von Interesse enthalten können, ohne die gesamte Tabelle zu scannen.

Es ist wichtig zu verstehen, dass ein Index den Datenzugriff bei bestimmten Wartungskosten beschleunigt. Für jede Operation an indizierten Daten, sei es das Einfügen, Löschen oder Aktualisieren von Tabellenzeilen, müssen auch die Indizes für diese Tabelle und in derselben Transaktion aktualisiert werden. Beachten Sie, dass die Aktualisierung von Tabellenfeldern, für die keine Indizes erstellt wurden, nicht zu einer Indexaktualisierung führt. Diese Technik wird als HOT (Heap-Only Tuples) bezeichnet.

Die Erweiterbarkeit hat einige Auswirkungen. Um das Hinzufügen einer neuen Zugriffsmethode zum System zu vereinfachen, wurde eine Schnittstelle der allgemeinen Indexierungs-Engine implementiert. Die Hauptaufgabe besteht darin, TIDs von der Zugriffsmethode abzurufen und mit ihnen zu arbeiten:

  • Lesen Sie Daten aus entsprechenden Versionen von Tabellenzeilen.
  • Abrufen von Zeilenversionen TID für TID oder in einem Stapel mithilfe einer vorgefertigten Bitmap.
  • Überprüfen Sie die Sichtbarkeit der Zeilenversionen für die aktuelle Transaktion unter Berücksichtigung ihrer Isolationsstufe.

Die Indizierungs-Engine ist an der Durchführung von Abfragen beteiligt. Es wird nach einem Plan aufgerufen, der in der Optimierungsphase erstellt wurde. Das Optimierungsprogramm, das verschiedene Methoden zum Ausführen der Abfrage aussortiert und bewertet, sollte die Funktionen aller potenziell anwendbaren Zugriffsmethoden verstehen. Kann die Methode Daten in der erforderlichen Reihenfolge zurückgeben oder sollten wir mit dem Sortieren rechnen? Können wir diese Methode verwenden, um nach NULL zu suchen? Dies sind Probleme, die der Optimierer regelmäßig löst.

Nicht nur der Optimierer benötigt Informationen zur Zugriffsmethode. Beim Erstellen eines Index muss das System entscheiden, ob der Index auf mehreren Spalten erstellt werden kann und ob dieser Index die Eindeutigkeit sicherstellt.

Daher sollte jede Zugriffsmethode alle erforderlichen Informationen über sich selbst bereitstellen. Versionen unter 9.6 verwendeten hierfür die Tabelle "pg_am", während ab Version 9.6 die Daten innerhalb spezieller Funktionen auf tiefere Ebenen verschoben wurden. Wir werden diese Schnittstelle etwas weiter kennenlernen.

Der Rest ist Aufgabe der Zugriffsmethode:

  • Implementieren Sie einen Algorithmus zum Erstellen des Index und ordnen Sie die Daten Seiten zu (damit der Puffer-Cache-Manager jeden Index einheitlich verarbeitet).
  • Suchen Sie Informationen im Index nach einem Prädikat in der Form " Ausdruck des Operators für indizierte Felder ".
  • Bewerten Sie die Indexnutzungskosten.
  • Bearbeiten Sie die für eine korrekte Parallelverarbeitung erforderlichen Sperren.
  • Generieren Sie WAL-Datensätze (Write-Ahead-Protokoll).

Wir werden zuerst die Funktionen der allgemeinen Indizierungs-Engine betrachten und dann verschiedene Zugriffsmethoden betrachten.

Indizierungsmaschine


Mit der Indizierungs-Engine kann PostgreSQL einheitlich mit verschiedenen Zugriffsmethoden arbeiten, wobei jedoch deren Funktionen berücksichtigt werden.

Haupt-Scan-Techniken


Index-Scan


Wir können anders mit TIDs arbeiten, die von einem Index bereitgestellt werden. Betrachten wir ein Beispiel:

postgres=# create table t(a integer, b text, c boolean); postgres=# insert into t(a,b,c) select s.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as s(id) order by random(); postgres=# create index on t(a); postgres=# analyze t;

Wir haben eine Tabelle mit drei Feldern erstellt. Das erste Feld enthält Zahlen von 1 bis 100.000, und für dieses Feld wird ein Index (unabhängig vom Typ) erstellt. Das zweite Feld enthält verschiedene ASCII-Zeichen außer nicht druckbaren. Schließlich enthält das dritte Feld einen logischen Wert, der für etwa 1% der Zeilen wahr und für den Rest falsch ist. Zeilen werden in zufälliger Reihenfolge in die Tabelle eingefügt.

Versuchen wir, einen Wert durch die Bedingung "a = 1" auszuwählen. Beachten Sie, dass die Bedingung wie " Operatorausdruck für indizierte Felder " aussieht, wobei der Operator "gleich" und der Ausdruck (Suchschlüssel) "1" ist. In den meisten Fällen muss die Bedingung so aussehen, damit der Index verwendet werden kann.

 postgres=# explain (costs off) select * from t where a = 1; 
  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t Index Cond: (a = 1) (2 rows)

In diesem Fall entschied sich der Optimierer für die Verwendung des Index-Scans . Beim Index-Scannen gibt die Zugriffsmethode nacheinander TID-Werte zurück, bis die letzte übereinstimmende Zeile erreicht ist. Die Indizierungs-Engine greift nacheinander auf die durch TIDs angegebenen Tabellenzeilen zu, ruft die Zeilenversion ab, überprüft ihre Sichtbarkeit anhand von Multiversions-Parallelitätsregeln und gibt die erhaltenen Daten zurück.

Bitmap-Scan


Der Index-Scan funktioniert einwandfrei, wenn nur wenige Werte verarbeitet werden. Wenn jedoch die Anzahl der abgerufenen Zeilen zunimmt, ist es wahrscheinlicher, dass Sie mehrmals zur gleichen Tabellenseite zurückkehren. Daher wechselt der Optimierer zum Bitmap-Scan .

 postgres=# explain (costs off) select * from t where a <= 100; 
  QUERY PLAN ------------------------------------ Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) (4 rows)

Die Zugriffsmethode gibt zuerst alle TIDs zurück, die der Bedingung entsprechen (Bitmap-Index-Scan-Knoten), und die Bitmap der Zeilenversionen wird aus diesen TIDs erstellt. Zeilenversionen werden dann aus der Tabelle gelesen (Bitmap Heap Scan), wobei jede Seite nur einmal gelesen wird.

Beachten Sie, dass im zweiten Schritt die Bedingung möglicherweise erneut überprüft wird (Cond erneut prüfen). Die Anzahl der abgerufenen Zeilen kann zu groß sein, als dass die Bitmap der Zeilenversionen vollständig in den RAM passen könnte (begrenzt durch den Parameter "work_mem"). In diesem Fall wird die Bitmap nur für Seiten erstellt, die mindestens eine übereinstimmende Zeilenversion enthalten. Diese "verlustbehaftete" Bitmap benötigt weniger Speicherplatz, aber beim Lesen einer Seite müssen wir die Bedingungen für jede dort enthaltene Zeile erneut überprüfen. Beachten Sie, dass selbst für eine kleine Anzahl von abgerufenen Zeilen und daher eine "exakte" Bitmap (wie in unserem Beispiel) der Schritt "Cond erneut prüfen" ohnehin im Plan dargestellt wird, obwohl er nicht tatsächlich ausgeführt wird.

Wenn Bedingungen für mehrere Tabellenfelder festgelegt und diese Felder indiziert werden, ermöglicht der Bitmap-Scan die gleichzeitige Verwendung mehrerer Indizes (wenn der Optimierer dies für effizient hält). Für jeden Index werden Bitmaps von Zeilenversionen erstellt, für die dann eine bitweise boolesche Multiplikation (wenn die Ausdrücke durch UND verbunden sind) oder eine boolesche Addition (wenn die Ausdrücke durch ODER verbunden sind) durchgeführt wird. Zum Beispiel:

 postgres=# create index on t(b); postgres=# analyze t; postgres=# explain (costs off) select * from t where a <= 100 and b = 'a'; 
  QUERY PLAN -------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) (7 rows)

Hier verbindet der BitmapAnd-Knoten zwei Bitmaps durch die bitweise "und" -Operation.

Mit dem Bitmap-Scan können wir wiederholte Zugriffe auf dieselbe Datenseite vermeiden. Was aber, wenn die Daten in Tabellenseiten physisch genauso geordnet sind wie Indexdatensätze? Es steht außer Zweifel, dass wir uns nicht vollständig auf die physische Reihenfolge der Daten auf den Seiten verlassen können. Wenn sortierte Daten benötigt werden, müssen wir die ORDER BY-Klausel in der Abfrage explizit angeben. Es ist jedoch wahrscheinlich, dass tatsächlich "fast alle" Daten geordnet sind: Zum Beispiel, wenn Zeilen in der erforderlichen Reihenfolge hinzugefügt werden und sich danach oder nach Ausführung des Befehls CLUSTER nicht ändern. In solchen Fällen ist das Erstellen einer Bitmap ein übermäßiger Schritt, und ein regelmäßiger Index-Scan ist genauso gut (es sei denn, wir berücksichtigen die Möglichkeit, mehrere Indizes zu verbinden). Daher untersucht der Planer bei der Auswahl einer Zugriffsmethode eine spezielle Statistik, die die Korrelation zwischen der physischen Zeilenreihenfolge und der logischen Reihenfolge der Spaltenwerte zeigt:

 postgres=# select attname, correlation from pg_stats where tablename = 't'; 
  attname | correlation ---------+------------- b | 0.533512 c | 0.942365 a | -0.00768816 (3 rows)

Absolutwerte nahe eins zeigen eine hohe Korrelation an (wie für Spalte "c"), während Werte nahe Null im Gegensatz dazu eine chaotische Verteilung anzeigen (Spalte "a").

Sequentieller Scan


Um das Bild zu vervollständigen, sollten wir beachten, dass der Optimierer bei einer nicht selektiven Bedingung zu Recht den sequentiellen Scan der gesamten Tabelle der Verwendung des Index vorzieht:

 postgres=# explain (costs off) select * from t where a <= 40000; 
  QUERY PLAN ------------------------ Seq Scan on t Filter: (a <= 40000) (2 rows)

Die Sache ist, dass Indizes umso besser funktionieren, je höher die Bedingungsselektivität ist, dh je weniger Zeilen damit übereinstimmen. Die Zunahme der Anzahl der abgerufenen Zeilen erhöht die Gemeinkosten für das Lesen von Indexseiten.

Sequentielle Scans sind schneller als zufällige Scans. Dies gilt insbesondere für Festplatten, bei denen der mechanische Vorgang, einen Magnetkopf auf eine Spur zu bringen, erheblich länger dauert als das Lesen der Daten selbst. Dieser Effekt macht sich bei SSD weniger bemerkbar. Zur Berücksichtigung der Unterschiede bei den Zugriffskosten stehen zwei Parameter zur Verfügung: "seq_page_cost" und "random_page_cost", die wir nicht nur global, sondern auch auf der Ebene der Tablespaces festlegen können, um sie an die Eigenschaften verschiedener Festplattensubsysteme anzupassen.

Indizes abdecken


In der Regel besteht die Hauptaufgabe einer Zugriffsmethode darin, die Bezeichner übereinstimmender Tabellenzeilen zurückzugeben, damit die Indexierungsmaschine die erforderlichen Daten aus diesen Zeilen lesen kann. Was aber, wenn der Index bereits alle für die Abfrage benötigten Daten enthält? Ein solcher Index wird als Abdeckung bezeichnet . In diesem Fall kann der Optimierer den Nur-Index-Scan anwenden:

 postgres=# vacuum t; postgres=# explain (costs off) select a from t where a < 100; 
  QUERY PLAN ------------------------------------ Index Only Scan using t_a_idx on t Index Cond: (a < 100) (2 rows)

Dieser Name kann darauf hindeuten, dass die Indizierungs-Engine überhaupt nicht auf die Tabelle zugreift und alle erforderlichen Informationen allein von der Zugriffsmethode erhält. Dies ist jedoch nicht genau der Fall, da Indizes in PostgreSQL keine Informationen speichern, mit denen wir die Zeilensichtbarkeit beurteilen können. Daher gibt eine Zugriffsmethode Versionen von Zeilen zurück, die der Suchbedingung entsprechen, unabhängig von ihrer Sichtbarkeit in der aktuellen Transaktion.

Wenn die Indizierungs-Engine jedoch jedes Mal in der Tabelle nach Sichtbarkeit suchen müsste, hätte sich diese Scanmethode nicht von einem regulären Index-Scan unterschieden.

Um das Problem zu lösen, verwaltet PostgreSQL für Tabellen eine sogenannte Sichtbarkeitskarte, in der das Staubsaugen die Seiten markiert, auf denen Daten nicht lange genug geändert wurden, damit diese Daten für alle Transaktionen unabhängig von Startzeit und Isolationsstufe sichtbar sind. Wenn sich die Kennung einer vom Index zurückgegebenen Zeile auf eine solche Seite bezieht, kann eine Sichtbarkeitsprüfung vermieden werden.

Daher erhöht regelmäßiges Staubsaugen die Effizienz der Abdeckindizes. Darüber hinaus berücksichtigt der Optimierer die Anzahl der toten Tupel und kann entscheiden, den Nur-Index-Scan nicht zu verwenden, wenn er hohe Overhead-Kosten für die Sichtbarkeitsprüfung vorhersagt.

Mit dem Befehl EXPLAIN ANALYZE können wir die Anzahl der erzwungenen Zugriffe auf eine Tabelle ermitteln:

 postgres=# explain (analyze, costs off) select a from t where a < 100; 
  QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1) Index Cond: (a < 100) Heap Fetches: 0 Planning time: 0.092 ms Execution time: 0.059 ms (5 rows)

In diesem Fall war es nicht erforderlich, auf die Tabelle zuzugreifen (Heap Fetches: 0), da gerade gesaugt wurde. Im Allgemeinen ist es umso besser, je näher diese Zahl an Null liegt.

Nicht alle Indizes speichern indizierte Werte zusammen mit Zeilenbezeichnern. Wenn die Zugriffsmethode die Daten nicht zurückgeben kann, kann sie nicht für Nur-Index-Scans verwendet werden.

PostgreSQL 11 hat eine neue Funktion eingeführt: INCLUDE-Indizes. Was ist, wenn es einen eindeutigen Index gibt, dem einige Spalten fehlen, die als Deckungsindex für eine Abfrage verwendet werden können? Sie können die Spalten nicht einfach zum Index hinzufügen, da dies die Eindeutigkeit beeinträchtigt. Die Funktion ermöglicht das Einschließen von Nicht-Schlüsselspalten , die die Eindeutigkeit nicht beeinflussen und nicht in Suchprädikaten verwendet werden können, aber dennoch nur Index-Scans ermöglichen. Der Patch wurde von meiner Kollegin Anastasia Lubennikova entwickelt.

Null


NULL-Werte spielen in relationalen Datenbanken eine wichtige Rolle, um einen nicht vorhandenen oder unbekannten Wert bequem darzustellen.

Aber ein besonderer Wert ist etwas Besonderes. Eine reguläre boolesche Algebra wird ternär; Es ist unklar, ob NULL kleiner oder größer als reguläre Werte sein soll (dies erfordert spezielle Konstrukte zum Sortieren, NULLS FIRST und NULLS LAST). Es ist nicht ersichtlich, ob Aggregatfunktionen NULL berücksichtigen sollten oder nicht. Für den Planer wird eine spezielle Statistik benötigt ...

Aus Sicht der Indexunterstützung ist auch unklar, ob wir diese Werte indizieren müssen oder nicht. Wenn NULL-Werte nicht indiziert sind, ist der Index möglicherweise kompakter. Wenn jedoch NULL-Werte indiziert werden, können wir den Index für Bedingungen wie "Indexfeld IS [NOT] NULL" und auch als Deckungsindex verwenden, wenn für die Tabelle überhaupt keine Bedingungen angegeben sind (da in diesem Fall die Der Index muss die Daten aller Tabellenzeilen zurückgeben, einschließlich derer mit NULL-Werten.

Für jede Zugriffsmethode treffen die Entwickler eine individuelle Entscheidung, ob NULLs indiziert werden sollen oder nicht. In der Regel werden sie jedoch indiziert.

Indizes für mehrere Felder


Um die Bedingungen für mehrere Felder zu unterstützen, können mehrspaltige Indizes verwendet werden. Zum Beispiel könnten wir einen Index für zwei Felder unserer Tabelle erstellen:

 postgres=# create index on t(a,b); postgres=# analyze t;

Der Optimierer wird diesen Index höchstwahrscheinlich dem Verbinden von Bitmaps vorziehen, da wir hier leicht die erforderlichen TIDs ohne Hilfsoperationen erhalten:

 postgres=# explain (costs off) select * from t where a <= 100 and b = 'a'; 
  QUERY PLAN ------------------------------------------------ Index Scan using t_a_b_idx on t Index Cond: ((a <= 100) AND (b = 'a'::text)) (2 rows)

Ein mehrspaltiger Index kann auch verwendet werden, um das Abrufen von Daten durch eine Bedingung für einige der Felder zu beschleunigen, beginnend mit dem ersten:

 postgres=# explain (costs off) select * from t where a <= 100; 
  QUERY PLAN -------------------------------------- Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_b_idx Index Cond: (a <= 100) (4 rows)

Wenn die Bedingung nicht für das erste Feld festgelegt ist, wird der Index im Allgemeinen nicht verwendet. Manchmal betrachtet der Optimierer die Verwendung des Index jedoch als effizienter als den sequentiellen Scan. Wir werden dieses Thema erweitern, wenn wir "btree" -Indizes betrachten.

Nicht alle Zugriffsmethoden unterstützen das Erstellen von Indizes für mehrere Spalten.

Indizes zu Ausdrücken


Wir haben bereits erwähnt, dass die Suchbedingung wie " Operatorausdruck für indizierte Felder " aussehen muss. Im folgenden Beispiel wird der Index nicht verwendet, da anstelle des Feldnamens selbst ein Ausdruck verwendet wird, der den Feldnamen enthält:

 postgres=# explain (costs off) select * from t where lower(b) = 'a'; 
  QUERY PLAN ------------------------------------------ Seq Scan on t Filter: (lower((b)::text) = 'a'::text) (2 rows)

Das Umschreiben dieser spezifischen Abfrage erfordert nicht viel, sodass nur der Feldname links vom Operator geschrieben wird. Ist dies jedoch nicht möglich, helfen Indizes für Ausdrücke (Funktionsindizes):

 postgres=# create index on t(lower(b)); postgres=# analyze t; postgres=# explain (costs off) select * from t where lower(b) = 'a'; 
  QUERY PLAN ---------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: (lower((b)::text) = 'a'::text) -> Bitmap Index Scan on t_lower_idx Index Cond: (lower((b)::text) = 'a'::text) (4 rows)

Der Funktionsindex basiert nicht auf einem Tabellenfeld, sondern auf einem beliebigen Ausdruck. Das Optimierungsprogramm berücksichtigt diesen Index für Bedingungen wie " Operatorausdruck für indizierte Ausdrücke ". Wenn die Berechnung des zu indizierenden Ausdrucks eine kostspielige Operation ist, erfordert die Aktualisierung des Index auch erhebliche Rechenressourcen.

Beachten Sie auch, dass für den indizierten Ausdruck eine individuelle Statistik erfasst wird. Wir können diese Statistik in der Ansicht "pg_stats" anhand des Indexnamens kennenlernen:

 postgres=# \dt 
  Table "public.t" Column | Type | Modifiers --------+---------+----------- a | integer | b | text | c | boolean | Indexes: "t_a_b_idx" btree (a, b) "t_a_idx" btree (a) "t_b_idx" btree (b) "t_lower_idx" btree (lower(b)) 
 postgres=# select * from pg_stats where tablename = 't_lower_idx';

Falls erforderlich, können Sie die Anzahl der Histogrammkörbe auf dieselbe Weise wie bei regulären Datenfeldern steuern (wobei zu beachten ist, dass der Spaltenname je nach indiziertem Ausdruck unterschiedlich sein kann):

 postgres=# \d t_lower_idx 
  Index "public.t_lower_idx" Column | Type | Definition --------+------+------------ lower | text | lower(b) btree, for table "public.t" 
 postgres=# alter index t_lower_idx alter column "lower" set statistics 69;

PostgreSQL 11 bietet eine übersichtlichere Methode zur Steuerung des Statistikziels für Indizes, indem die Spaltennummer im Befehl ALTER INDEX ... SET STATISTICS angegeben wird. Der Patch wurde von meinem Kollegen Alexander Korotkov und Adrien Nayrat entwickelt.

Teilindizes


Manchmal besteht die Notwendigkeit, nur einen Teil der Tabellenzeilen zu indizieren. Dies hängt normalerweise mit einer sehr ungleichmäßigen Verteilung zusammen: Es ist sinnvoll, anhand eines Index nach einem seltenen Wert zu suchen, aber es ist einfacher, einen häufigen Wert durch vollständiges Scannen der Tabelle zu finden.

Wir können sicherlich einen regulären Index für die Spalte "c" erstellen, der so funktioniert, wie wir es erwarten:

 postgres=# create index on t(c); postgres=# analyze t; postgres=# explain (costs off) select * from t where c; 
  QUERY PLAN ------------------------------- Index Scan using t_c_idx on t Index Cond: (c = true) Filter: c (3 rows) 
 postgres=# explain (costs off) select * from t where not c; 
  QUERY PLAN ------------------- Seq Scan on t Filter: (NOT c) (2 rows)

Und die Indexgröße beträgt 276 Seiten:

 postgres=# select relpages from pg_class where relname='t_c_idx'; 
  relpages ---------- 276 (1 row)

Da die Spalte "c" jedoch nur für 1% der Zeilen den Wert "true" hat, werden 99% des Index tatsächlich nie verwendet. In diesem Fall können wir einen Teilindex erstellen:

 postgres=# create index on t(c) where c; postgres=# analyze t;

Die Größe des Index wird auf 5 Seiten reduziert:

 postgres=# select relpages from pg_class where relname='t_c_idx1'; 
  relpages ---------- 5 (1 row)

Manchmal kann der Unterschied in Größe und Leistung ziemlich bedeutend sein.

Sortieren


Wenn eine Zugriffsmethode Zeilenbezeichner in einer bestimmten Reihenfolge zurückgibt, bietet dies dem Optimierer zusätzliche Optionen zum Ausführen der Abfrage.

Wir können die Tabelle scannen und dann die Daten sortieren:

 postgres=# set enable_indexscan=off; postgres=# explain (costs off) select * from t order by a; 
  QUERY PLAN --------------------- Sort Sort Key: a -> Seq Scan on t (3 rows)

Aber wir können die Daten unter Verwendung des Index leicht in einer gewünschten Reihenfolge lesen:

 postgres=# set enable_indexscan=on; postgres=# explain (costs off) select * from t order by a; 
  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t (1 row)

Nur "btree" aller Zugriffsmethoden kann sortierte Daten zurückgeben. Lassen Sie uns daher eine ausführlichere Diskussion verschieben, bis dieser Indextyp berücksichtigt wird.

Gleichzeitiges Bauen


Normalerweise erhält das Erstellen eines Index eine SHARE-Sperre für die Tabelle. Diese Sperre ermöglicht das Lesen von Daten aus der Tabelle, verhindert jedoch Änderungen während der Indexerstellung.

Dies können wir sicherstellen, wenn wir beispielsweise beim Erstellen eines Index für die Tabelle "t" die folgende Abfrage in einer anderen Sitzung ausführen:

 postgres=# select mode, granted from pg_locks where relation = 't'::regclass; 
  mode | granted -----------+--------- ShareLock | t (1 row)

Wenn die Tabelle groß genug ist und häufig zum Einfügen, Aktualisieren oder Löschen verwendet wird, scheint dies unzulässig zu sein, da Änderungsprozesse lange auf eine Sperrfreigabe warten.

In diesem Fall können wir gleichzeitig einen Index erstellen.

 postgres=# create index concurrently on t(a);

Dieser Befehl sperrt die Tabelle im SHARE UPDATE EXCLUSIVE-Modus, der sowohl das Lesen als auch das Aktualisieren ermöglicht (nur das Ändern der Tabellenstruktur ist verboten sowie das gleichzeitige Staubsaugen, Analysieren oder Erstellen eines anderen Index für diese Tabelle).

Es gibt jedoch auch eine Kehrseite. Erstens wird der Index langsamer als gewöhnlich erstellt, da zwei Durchgänge über die Tabelle anstelle von einem durchgeführt werden, und es ist auch erforderlich, auf den Abschluss paralleler Transaktionen zu warten, die die Daten ändern.

Zweitens kann bei gleichzeitiger Erstellung des Index ein Deadlock auftreten oder eindeutige Einschränkungen können verletzt werden. Der Index wird jedoch erstellt, obwohl er nicht funktioniert. Ein solcher Index muss gelöscht und neu erstellt werden. Nicht betriebsbereite Indizes werden in der Ausgabe des Befehls psql \ d mit dem Wort INVALID markiert, und die folgende Abfrage gibt eine vollständige Liste dieser zurück:

 postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name from pg_index where not indisvalid; 
  index_name | table_name ------------+------------ t_a_idx | t (1 row)

Lesen Sie weiter .

Source: https://habr.com/ru/post/de441962/

More articles:


All Articles