Der Deckungsindex ist nicht nur eine weitere nĂŒtzliche Funktion. Dieses Ding ist rein praktisch. Ohne sie kann Index Only Scan möglicherweise keinen Gewinn bringen. Obwohl der Deckungsindex in verschiedenen Situationen auf unterschiedliche Weise wirksam ist.
Hier geht es nicht wirklich darum, Indizes abzudecken: Genau genommen sind in Postgres die sogenannten Inklusivindizes erschienen. Aber in der Reihenfolge: Ein Deckungsindex ist ein Index, der alle fĂŒr die Abfrage erforderlichen Spaltenwerte enthĂ€lt. Der Zugriff auf die Tabelle selbst ist jedoch nicht mehr erforderlich. Fast. In einem Artikel von
Yegor Rogov , der in seiner Indexreihe mit 10 (!) Teilen enthalten ist, können
Sie ĂŒber âfastâ und andere Nuancen
lesen . Der
Inklusivindex wurde speziell fĂŒr die Suche in typischen Abfragen erstellt: Die Werte von Feldern, die nicht durchsucht werden können, werden dem Suchindex hinzugefĂŒgt. Sie werden nur benötigt, um nicht erneut auf die Tabelle zu verweisen. Solche Indizes werden mit dem SchlĂŒsselwort INCLUDE gebildet.
Anastasia Lubennikova (Postgres Professional) hat die btree-Methode fertiggestellt, damit zusĂ€tzliche Spalten in den Index aufgenommen werden können. Dieser Patch war in PostgreSQL 11 enthalten. Die Patches fĂŒr die GiST / SP-GiST-Zugriffsmethoden hatten jedoch vor der Veröffentlichung dieser Version keine Zeit zum Reifen. Bis zum 12. GiST reifte.
Der konstruktive Wunsch nach integrativen Indizes fĂŒr GiST entstand vor langer Zeit: Mitte April 2018 wurde
der Community ein Test-Patch von Andrey Borodin
angeboten . Er hat alle grundlegenden, sehr schwierigen Arbeiten erledigt.
Anfang August 2019 fĂŒgte Alexander Korotkov kosmetische Verbesserungen hinzu und verpflichtete den Patch.
Zur Demonstration und fĂŒr einige Nachforschungen werden wir einen Satz von 3 Millionen Rechtecken generieren. Gleichzeitig ein paar Worte zum Box-Typ, da nicht alle Manipulationen damit intuitiv sind.
Die Art der Box - das heiĂt das Rechteck - ist seit langem in Postgres vorhanden. Sie wird durch 2 Punkte (den geometrischen Typpunkt) definiert - die entgegengesetzten Eckpunkte des Rechtecks ââ(dh das Rechteck kann nicht schrĂ€g und seitlich verstreut sein). Wir lesen in der
Dokumentation : âWerte vom Typ box werden in einer der folgenden Formen geschrieben:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
In der Praxis muss man beispielsweise so schreiben:
SELECT box('1,2', '3,4'); box
Zuerst zeigt uns Postgres den oberen rechten Scheitelpunkt, dann den unteren linken. Wenn wir so schreiben,
SELECT box('5,2', '3,4'); box
dann werden wir sicherstellen, dass Postgres nicht die Spitzen gab, die sie ihm gaben. Er berechnete die obere rechte und untere linke von unserer oberen linken und unteren rechten. Dies ist eine praktische Eigenschaft, wenn die Position der Scheitelpunkte nicht im Voraus bekannt ist - beispielsweise bei zufÀlliger Erzeugung. Die Notation '1,2', '3,4' entspricht Punkt (1,2), Punkt (3,4). Diese Form ist manchmal bequemer.
FĂŒr Unternehmen: Suchen Sie in 3 Millionen Rechtecken
CREATE TABLE boxes(id serial, thebox box, name text);
Wir werden 3 Millionen zufÀllige Rechtecke erzeugen. Wir wollen eine Normalverteilung, aber um die
tablefunc- Erweiterung nicht zu verwenden, verwenden wir den âschlechtenâ Ansatz: Wir verwenden random () - random (), was auch ein schönes Bild ergibt (siehe Abb.). Bei Rechtecken sind sie umso nĂ€her an der Mitte, je gröĂer sie sind. Ihre Schwerpunkte sind ebenfalls zufĂ€llig. Solche Verteilungen sind charakteristisch fĂŒr einige Arten von realen Stadtdaten. Und wer sich mit den Gesetzen der Statistik befassen oder Erinnerungen auffrischen möchte, kann
hier zum Beispiel ĂŒber den Unterschied von Zufallsvariablen lesen.
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
Die GröĂe der Tabelle mit
\dt+ betrÀgt 242 MB. Jetzt können Sie die Suche starten.
Wir suchen ohne Index:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Wir sehen, dass es einen parallelen Seq-Scan gibt - einen sequentiellen Scan (wenn auch parallelisiert).
Erstellen Sie einen regulÀren, nicht inklusiven Index:
CREATE INDEX ON boxes USING gist(thebox);
Die GröĂe des Index
boxes_thebox_idx , der
\di+ anzeigt, 262 MB. Als Antwort auf dieselbe Anfrage erhalten wir:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Die Suchzeit wurde um den Faktor drei reduziert, und anstelle des parallelen Seq-Scans erhielten sie einen Bitmap-Index-Scan. Es parallelisiert nicht, arbeitet aber schneller.
Töte nun den alten Index und erstelle einen inklusiven:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
Index der
boxes_thebox_name_idx fetteren: 356MB. Lass uns gehen:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Nur Index-Scan wird verwendet, aber das Bild ist traurig: Die Zeit ist fast zweimal lÀnger als ohne. Wir lesen das Handbuch des Erstellers der Indizes in
Teil I :
âčRang PostgreSQL-Indizes enthalten keine Informationen, mit denen Sie die Sichtbarkeit von Zeilen beurteilen können. Daher gibt die Zugriffsmethode alle Versionen von Zeilen zurĂŒck, die unter die Suchbedingung fallen, unabhĂ€ngig davon, ob sie fĂŒr die aktuelle Transaktion sichtbar sind oder nicht. Wenn der Indizierungsmechanismus jedoch jedes Mal in die Tabelle schauen mĂŒsste, um die Sichtbarkeit zu bestimmen, wĂŒrde sich diese Scanmethode nicht von der normalen Indexscanung unterscheiden. Das Problem wird durch die Tatsache gelöst, dass PostgreSQL die sogenannte Sichtbarkeitskarte fĂŒr Tabellen unterstĂŒtzt, in der der Vakuumprozess Seiten markiert, auf denen sich Daten nicht lange genug geĂ€ndert haben, damit alle Transaktionen sie sehen können, unabhĂ€ngig von Startzeit und Isolationsstufe. Wenn sich die Kennung der vom Index zurĂŒckgegebenen Zeile auf eine solche Seite bezieht, kann die Sichtbarkeit nicht ĂŒberprĂŒft werden. âșâș
Wir machen VAKUUM. Wiederholen:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Eine ganz andere Sache! Zweimal so viel Gewinn wie im Non-Inclusive-Index.
SelektivitÀt und Gewinn
Die Leistung inklusiver Indizes hĂ€ngt stark von der SelektivitĂ€t der Bedingungen in Abfragen ab. Um diese AbhĂ€ngigkeit ein wenig zu untersuchen, werden wir das umgekehrte Problem lösen: Wir werden eine Beschriftung mit einem Index vom Typ Punkt generieren und wir werden suchen, wie viele Punkte in das gegebene Feld fallen. Verteilen Sie die Punkte gleichmĂ€Ăig im Quadrat.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
Die GröĂe der Tabelle betrĂ€gt 211 MB.
CREATE INDEX on test_covergist USING gist(tochka);
GröĂe 213 MB.
Wir werden natĂŒrlich alle verfĂŒgbaren Punkte auf ein Quadrat setzen:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Wir haben EXPLAIN gebeten, die Puffer anzuzeigen. Es wird nĂŒtzlich sein. Jetzt betrĂ€gt die AusfĂŒhrungszeit der Anforderung mehr als 2 Sekunden. Es ist ersichtlich, dass Puffer: gemeinsam gelesen = 54287. In einer anderen Situation konnte eine Mischung aus gemeinsamem Lesen und gemeinsamem Treffer angezeigt werden. Das heiĂt, einige Puffer werden von der Festplatte (oder aus dem Betriebssystem-Cache) und andere aus dem Puffer-Cache gelesen. Wir kennen die ungefĂ€hre GröĂe der Tabelle und der Indizes und schĂŒtzen uns daher, indem wir gemeinsam genutzte Puffer so einstellen, dass alles passt. Starten Sie Postgres mit der Option neu
-o "-c shared_buffers=1GB"
Jetzt:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Das heiĂt, das gemeinsame Lesen wurde zu einem gemeinsamen Treffer, und die Zeit wurde dreimal reduziert.
Ein weiteres wichtiges Detail in EXPLAIN: 3 Millionen Punkte werden zurĂŒckgegeben, und die Prognose fĂŒr die zurĂŒckgegebene Anzahl von DatensĂ€tzen betrĂ€gt 3.000. Spoiler: Diese Anzahl Ă€ndert sich bei keiner SelektivitĂ€t. Der Optimierer weiĂ nicht, wie die KardinalitĂ€t bei der Arbeit mit Box- oder Punkttypen bewertet werden soll. Und der Plan wird sich nicht Ă€ndern: FĂŒr jede GröĂe des Rechtecks ââwird ein Bitmap-Index-Scan fĂŒr test_covergist_tochka_idx durchgefĂŒhrt.
Hier sind zwei weitere Messungen mit der Anzahl der ausgegebenen DatensĂ€tze, die sich um GröĂenordnungen unterscheiden:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Es werden 10-mal weniger DatensĂ€tze zurĂŒckgegeben (tatsĂ€chliche ... Zeilen = 269882), die Zeit hat sich um etwa das 5-fache verringert.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
Der Inhalt eines 30K à 30K-Quadrats (2780) wird in nur 16 ms gezÀhlt. Und wenn es Dutzende von DatensÀtzen gibt, werden sie bereits in Bruchteilen von ms extrahiert, und solche Messungen sind nicht sehr zuverlÀssig.
Messen Sie schlieĂlich dasselbe mit dem Inklusivindex:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
GröĂe 316 MB.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Die Zeit ist fast dieselbe wie bei einem herkömmlichen Index, obwohl nur Index scannen.
Aber:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Und es war 151 ms. Und dementsprechend:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Dies ist bereits ein Bruchteil von ms fĂŒr dieselben 2780-Punkt-DatensĂ€tze.
Puffer wie Waffen
Eine ErklĂ€rung kann in einer Schrotflinte gesucht und gefunden werden, die noch nicht geschossen hat, aber an der Wand hing: die Anzahl der gelesenen Blöcke. Bei einem inklusiven Index werden nur Blöcke des Index selbst gelesen (Heap Fetches: 0). In drei FĂ€llen waren dies die Nummern 40492, 3735 und 52. Bei Verwendung des regulĂ€ren Index bestehen die gelesenen Blöcke jedoch aus der Summe der im Bitmap-Heap-Scan-Index (54248 mit 3 Millionen DatensĂ€tzen) gelesenen Bits und derjenigen, die aus dem Heap gelesen werden mussten (27223). , da das Namensfeld nicht aus einem regulĂ€ren Index extrahiert werden kann. 54248 + 27223 = 81471. Das Exklusive war 40492. FĂŒr zwei andere FĂ€lle: 29534 + 2510 = 31044 und 2655 + 31 = 2686. Im Fall eines regulĂ€ren Index werden ohnehin mehr Blöcke gelesen, aber mit einer Verbesserung der SelektivitĂ€t beginnt sich die Anzahl der gelesenen Blöcke eher um GröĂenordnungen als um das Zweifache zu unterscheiden, da die Anzahl der erforderlichen Blöcke aus einem Heap langsamer abnimmt als das Lesen von Indexblöcken.
Aber vielleicht geht es ĂŒberhaupt nicht um SelektivitĂ€t, sondern einfach um die GröĂe der Tabelle? FĂŒr alle FĂ€lle wiederholen wir dieselben Schritte und generieren eine Tabelle mit 300.000 und nicht 3 Millionen DatensĂ€tzen:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Wiederholen Sie dies als NĂ€chstes fĂŒr den Inklusivindex. Hier sind die Ergebnisse:
Bei einer 100% igen Punktabdeckung war die Abfrage sogar etwas langsamer als mit dem ĂŒblichen Index. Weiter, wie im Fall von 3 Millionen, passte alles zusammen. Das heiĂt, SelektivitĂ€t ist wichtig.
Unser Unternehmen testete inklusive GiST-Indizes an realen Daten - ein Satz mit mehreren Millionen Rechtecken auf einer Karte von Moskau. Die Schlussfolgerung ist dieselbe: In vielen Situationen beschleunigen solche Indizes Abfragen spĂŒrbar. Der Artikel kann jedoch nicht mit Bildern und Testzahlen illustriert werden: Diese Daten sind nicht gemeinfrei.
Anstelle einer Schlussfolgerung
Kehren wir fĂŒr einen Moment zu zufĂ€lligen Rechtecken zurĂŒck. Versuchen wir, dasselbe mit spgist zu tun. Sie können sich daran erinnern oder herausfinden, was es heiĂt, die Unterschiede zwischen SP-GiST und GiST zu verstehen, indem Sie den Artikel
Indizes in PostgreSQL - 6 lesen. Erstellen Sie einen Inklusivindex:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
Leider sind fĂŒr SP-GiST inklusive Indizes noch nicht implementiert.
Es gibt also Raum fĂŒr Verbesserungen!