Vor einigen Monaten — einen öffentlichen für PostgreSQL.
In der Zwischenzeit haben Sie ihn bereits über 6000 Mal genutzt, aber eine der nützlichen Funktionen könnte unbemerkt geblieben sein — das sind strukturelle Hinweise, die ungefähr so aussehen:
Achten Sie darauf, denn Ihre Abfragen werden dadurch "glatt und geschmeidig". 🙂
Im Ernst, viele Situationen, die eine Abfrage langsam und "ressourcenhungrig" machen, sind typisch und können anhand der Struktur und der Daten des Plans erkannt werden..
In diesem Fall muss jeder einzelne Entwickler nicht selbst nach einer Optimierungsmöglichkeit suchen und sich nur auf seine Erfahrung stützen — wir können ihm sagen, was hier passiert, worin das Problem liegen könnte und wie er an die Lösung herangehen kann.Das haben wir auch getan.
Lassen Sie uns die Fälle etwas genauer betrachten — wie sie identifiziert werden und zu welchen Empfehlungen sie führen.
Um tiefer in das Thema einzutauchen, können Sie zunächst einen entsprechenden Abschnitt aus anhören, bevor wir zu einer detaillierten Analyse jedes Beispiels übergehen:
#1: индексная «недосортировка»
Wann tritt dies auf?
Letzte Rechnung des Kunden „ООО Колокольчик“ anzeigen.
Wie man erkennt
-> Limit
-> Sortieren
-> Index [Nur] Rückwärts-Scan | Bitmap Heap Scan
Empfehlungen
Verwendeter Index mit Sortierfeldern erweitern.
Beispiel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "Fakten"
, (random() * 1000)::integer fk_cli; -- 1K verschiedene Fremdschlüssel
CREATE INDEX ON tbl(fk_cli); -- Index für den Fremdschlüssel
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- Auswahl nach spezifischer Beziehung
ORDER BY
pk DESC -- wir möchten nur einen "letzten" Eintrag
LIMIT 1; 
Es ist sofort erkennbar, dass über den Index mehr als 100 Einträge ausgelesen wurden, die dann alle sortiert und anschließend nur einer behalten wurde.
Wir korrigieren:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- Sortierschlüssel hinzugefügt
Selbst bei dieser primitiven Auswahl — 8,5 Mal schneller und 33 Mal weniger Lesevorgänge.. Der Effekt wird umso deutlicher, je mehr „Fakten“ Sie für jeden Wert haben. fk.
Ich möchte darauf hinweisen, dass dieser Index nicht schlechter als der frühere als „präfixbasiert“ auch bei anderen Abfragen funktioniert, fk, wo es keine Sortierungen nach pk gab und gibt (mehr dazu können Sie lesen ). Er wird auch die ordnungsgemäße Unterstützung des expliziten Fremdschlüssels für dieses Feld gewährleisten.
#2: пересечение индексов (BitmapAnd)
Wann tritt dies auf?
Alle Verträge für den Kunden „OOO Kolokolchik“, die im Namen von „NAO Lyutik“ abgeschlossen wurden, anzeigen.
Wie man erkennt
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanEmpfehlungen
Erstellen Sie komplexer Index Über die Felder beider Tabellen oder einen der bestehenden Felder aus der zweiten Tabelle erweitern.
Beispiel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "Fakten"
, (random() * 100)::integer fk_org -- 100 verschiedene Fremdschlüssel
, (random() * 1000)::integer fk_cli; -- 1K verschiedene Fremdschlüssel
CREATE INDEX ON tbl(fk_org); -- Index für den Fremdschlüssel
CREATE INDEX ON tbl(fk_cli); -- Index für den Fremdschlüssel
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- Auswahl nach spezifischem Paar 
Wir korrigieren:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Hier ist der Gewinn geringer, da der Bitmap Heap Scan an sich bereits effizient ist. Dennoch sieben Mal schneller und mit 2,5 Mal weniger Lesevorgängen.
#3: объединение индексов (BitmapOr)
Wann tritt dies auf?
Die ersten 20 ältesten „eigenen“ oder nicht zugewiesenen Anträge zur Bearbeitung anzeigen, wobei die eigenen Vorrang haben.
Wie man erkennt
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanEmpfehlungen
Verwenden UNION [ALL] zum Zusammenführen von Unterabfragen für jeden der OR-Bedingungen.
Beispiel:
ERSTELLEN SIE EINE TABELLE tbl ALS
SELECT
generate_series(1, 100000) pk -- 100K "Fakten"
, FALLS
random() < 1::real/16 THEN NULL -- mit einer Wahrscheinlichkeit von 1:16 ist der Eintrag "Unentschieden"
SONST (random() * 100)::integer -- 100 verschiedene Fremdschlüssel
ENDE fk_own;
EINDEDEXTERN AUF tbl(fk_own, pk); -- Index mit "anscheinend passender" Sortierung
SELECT
*
FROM
tbl
WHERE
fk_own = 1 ODER -- eigene
fk_own IST NULL -- ... oder "Unentschieden"
ORDER BY
pk
, (fk_own = 1) DESC -- zuerst "eigene"
LIMIT 20;
Wir korrigieren:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- zuerst "eigene" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IST NULL -- dann "Unentschieden" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- aber insgesamt - 20, mehr brauchen wir nicht 
Wir haben genutzt, dass alle 20 benötigten Datensätze sofort im ersten Block erhalten wurden, daher wurde der zweite, mit dem "teurerem" Bitmap Heap Scan, nicht einmal ausgeführt — letztendlich 22 Mal schneller, bei 44 Mal weniger Lesevorgängen!
Eine detaillierte Erzählung über diese Optimierungsmethode an konkreten Beispielen kann in den Artikeln gelesen werden und .
Eine verallgemeinerte Version der geordneten Auswahl nach mehreren Schlüsseln (und nicht nur nach einem Paar const/NULL) wird in dem Artikel behandelt .
#4: читаем много лишнего
Wann tritt dies auf?
In der Regel tritt es auf, wenn der Wunsch besteht, "ein weiteres Filterkriterium" an die bereits bestehende Abfrage anzuhängen.
"Haben Sie nicht so etwas ähnliches, aber mit perlweissen Knöpfen?» Film „Die diamantene Hand“
Zum Beispiel, indem wir die oben genannte Aufgabe modifizieren, zeigen Sie die ersten 20 ältesten „kritischen“ Anträge zur Bearbeitung, unabhängig von ihrer Zuordnung.
Wie man erkennt
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × Zeilen < RRbF -- mehr als 80 % des Gelesenen gefiltert
&& Schleifen × RRbF > 100 -- und dabei insgesamt mehr als 100 Einträge
Empfehlungen
Erstellen Sie [eine] spezialisierte Index mit einer WHERE-Bedingung oder fügen Sie weitere Felder in den Index ein.
Wenn die Filterbedingung „statisch“ für Ihre Aufgaben ist – das heißt, keine Erweiterung der Werte-Liste in der Zukunft vorsieht – ist es besser, einen WHERE-Index zu verwenden. In diese Kategorie fallen gut verschiedene boolean/enum-Status.
Wenn jedoch die Filterbedingung verschiedene Werte annehmen kann, ist es besser, den Index um diese Felder zu erweitern – wie im Fall von BitmapAnd oben.
Beispiel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "Fakten"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 verschiedene Fremdschlüssel
END fk_own
, (random() < 1::real/50) kritisch; -- 1:50, dass der Antrag "kritisch" ist
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
kritisch
ORDER BY
pk
LIMIT 20; 
Wir korrigieren:
CREATE INDEX ON tbl(pk)
WHERE kritisch; -- hinzugefügt "statische" Filterbedingung
Wie wir sehen, ist die Filterung aus dem Plan vollständig weggefallen, und die Anfrage wurde fünf Mal schneller.
#5: разреженная таблица
Wann tritt dies auf?
Verschiedene Versuche, eine eigene Warteschlange zur Aufgabenbearbeitung zu erstellen, wenn eine große Anzahl von Aktualisierungen/Löschungen in der Tabelle zu einer Situation mit vielen "toten" Datensätzen führt.
Wie man erkennt
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& Schleifen × (Zeilen + RRbF) 64
Empfehlungen
Regelmäßig manuell durchführen VACUUM [FULL] oder eine angemessen häufige Ausführung erreichen durch Feinabstimmung seiner Parameter, einschließlich .
In den meisten Fällen werden solche Probleme durch eine schlechte Anordnung der Anfragen bei Aufrufen aus der Geschäftslogik, wie die, die in .
Aber man muss verstehen, dass selbst VACUUM FULL nicht immer helfen kann. In solchen Fällen sollte man sich mit dem Algorithmus aus dem Artikel vertraut machen .
#6: чтение с «середины» индекса
Wann tritt dies auf?
Es scheint, als hätten wir ein wenig gelesen, und alles läuft über den Index, und wir haben nichts überflüssiges gefiltert – trotzdem wurden deutlich mehr Seiten gelesen, als gewünscht.
Wie man erkennt
-> Index [Only] Scan [Backward]
&& Schleifen × (Zeilen + RRbF) 64
Empfehlungen
Achten Sie genau auf die Struktur des verwendeten Index und die Schlüssel, die in der Abfrage angegeben sind – sehr wahrscheinlich, ist ein Teil des Index nicht definiert. Möglicherweise müssen Sie einen ähnlichen Index erstellen, jedoch ohne Präfixfelder oder .
Beispiel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "Fakten"
, (random() * 100)::integer fk_org -- 100 verschiedene Fremdschlüssel
, (random() * 1000)::integer fk_cli; -- 1K verschiedene Fremdschlüssel
CREATE INDEX ON tbl(fk_org, fk_cli); -- alles fast wie in #2
-- nur haben wir den separaten Index für fk_cli als überflüssig erachtet und entfernt
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- fk_org ist nicht definiert, obwohl früher im Index angegeben
LIMIT 20; 
Es scheint alles in Ordnung zu sein, sogar nach dem Index, aber irgendwie verdächtig – für jeden der 20 gelesenen Datensätze mussten 4 Seiten Daten gelesen werden, 32KB pro Datensatz – ist das nicht ein bisschen viel? Und der Name des Indexes tbl_fk_org_fk_cli_idx regt zum Nachdenken an.
Wir korrigieren:
CREATE INDEX ON tbl(fk_cli); 
Plötzlich – 10-mal schneller, und 4-mal weniger zu lesen!
Weitere Beispiele für ineffiziente Nutzung von Indizes finden Sie im Artikel .
#7: CTE × CTE
Wann tritt dies auf?
In der Abfrage haben wir 'fette' CTE aus verschiedenen Tabellen erfasst und danach entschieden, zwischen ihnen JOIN.
Der Fall ist relevant für Versionen unter v12 oder Abfragen mit WITH MATERIALIZED.
Wie man erkennt
-> CTE Scan
&& Schleifen > 10
&& Schleifen × (Zeilen + RRbF) > 10000
-- zu großes kartesisches Produkt von CTE
Empfehlungen
Die Abfrage sorgfältig analysieren — ob ? Если все-таки да, то ''Wortschatz'' in hstore/json anwenden nach dem Modell, das in .
#8: swap на диск (temp written)
Wann tritt dies auf?
Die einmalige Bearbeitung (Sortierung oder Einzigartigkeit) einer großen Menge von Datensätzen passt nicht in den dafür reservierten Speicher.
Wie man erkennt
-> *
&& temp geschrieben > 0Empfehlungen
Wenn der von der Operation verwendete Speicher die festgelegte Wertgrenze des Parameters nicht erheblich überschreitet, sollte er angepasst werden. Dies kann entweder global in der Konfiguration oder über SET [LOCAL] für eine bestimmte Abfrage/transaktion erfolgen.
Beispiel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Wir korrigieren:
SET work_mem = '128MB'; -- vor Ausführung der Abfrage 
Aus offensichtlichen Gründen, wenn nur der Speicher und nicht die Festplatte verwendet wird, wird die Abfrage viel schneller ausgeführt. Gleichzeitig wird auch ein Teil der Last von der HDD abgenommen.
Es ist wichtig zu verstehen, dass man nicht immer unbegrenzt viel Speicher zuweisen kann – es wird einfach nicht für alle ausreichen.
#9: неактуальная статистика
Wann tritt dies auf?
Die Datenbank wurde sofort mit vielen Daten gefüllt, doch es blieb keine Zeit, um sie zu verarbeiten. ANALYSE.
Wie man erkennt
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& Verhältnis >> 10Empfehlungen
Tatsächlich durchführen ANALYSE.
Diese Situation ist ausführlicher beschrieben in .
#10: «что-то пошло не так»
Wann tritt dies auf?
Es kam zu einer Blockierung durch eine konkurrierende Abfrage oder es standen nicht genügend CPU-/Hypervisor-Ressourcen zur Verfügung.
Wie man erkennt
-> *
&& (shared hit / 8K) + (shared read / 1K) 100ms -- wir haben wenig gelesen, aber zu lange
Empfehlungen
Verwenden Sie ein externes System zur Überwachung des Servers auf mögliche Blockierungen oder abnormalen Ressourcenverbrauch. Wir haben bereits über unsere Lösung zur Organisation dieses Prozesses für Hunderte von Servern berichtet. und .
Quelle: habr.com
