In der Welt von Postgres sind Indizes entscheidend für eine effiziente Navigation im Datenbankspeicher (auch als „Heap“ bezeichnet). Postgres unterstützt dafür keine Clusterung, und die MVCC-Architektur führt dazu, dass viele Versionen desselben Tupels angesammelt werden. Daher ist es sehr wichtig, effektive Indizes zu erstellen und zu pflegen, um Anwendungen zu unterstützen.
Ich präsentiere Ihnen einige Tipps zur Optimierung und Verbesserung der Nutzung von Indizes.
Hinweis: Die nachstehenden Abfragen funktionieren auf der unmodifizierten .
Verwendung von abdeckenden Indizes (Covering Indexes)
Betrachten wir eine Abfrage zum Abrufen der E-Mail-Adressen inaktiver Nutzer. In der Tabelle customer gibt es eine Spalte aktiv, und die Abfrage ist recht einfach:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows) In der Abfrage wird eine vollständige sequenzielle Tabellenscan durchgeführt customer. Lassen Sie uns einen Index für die Spalte erstellen aktiv:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0,28..12,29 rows=15 width=32)
Index Cond: (active = 0)
(2 Zeilen) Es hat geholfen, die folgende Abfrage wurde zu „Index-Scan“ geändert. Das bedeutet, dass Postgres den Index „idx_cust1“ scannen wird und anschließend mit der Suche in der Tabelle fortfahren wird, um die Werte anderer Spalten (in diesem Fall die Spalte von) zu lesen, die für die Anfrage benötigt werden.
In PostgreSQL 11 wurden überdeckende Indizes eingeführt. Diese ermöglichen es, eine oder mehrere zusätzliche Spalten in den Index aufzunehmen – deren Werte werden im Indexdatenlager gespeichert.
Wenn wir diese Funktionalität nutzen und den Wert der E-Mail-Adresse in den Index einfügen würden, müsste Postgres den Wert nicht in der Tabelle suchen. vonSehen wir, ob das funktioniert:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0,28..12,29 rows=15 width=32)
Index Cond: (active = 0)
(2 Zeilen) «Index Only Scan» sagt uns, dass die Anfrage nun nur noch einen Index benötigt, was hilft, alle Festplatten-I/O-Operationen zum Lesen der Tabellenmenge zu vermeiden.
Derzeit sind abdeckende Indizes nur für B-Bäume verfügbar. In diesem Fall wird der Pflegeaufwand jedoch höher sein.
Verwendung von partiellen Indizes
Partielle Indizes indexieren nur eine Teilmenge der Tabellenzeilen. Dies spart den Speicherplatz der Indizes und ermöglicht schnellere Scans.
Angenommen, wir müssen eine Liste der E-Mail-Adressen unserer Kunden aus Kalifornien abrufen. Die Anfrage lautet:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
was einen Abfrageplan hat, der beide verbundenen Tabellen scannt:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
ABFRAGEPLAN
----------------------------------------------------------------------
Hash Join (Kosten=15.65..32.22 Zeilen=9 Breite=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan auf customer c (Kosten=0.00..14.99 Zeilen=599 Breite=34)
-> Hash (Kosten=15.54..15.54 Zeilen=9 Breite=4)
-> Seq Scan auf address a (Kosten=0.00..15.54 Zeilen=9 Breite=4)
Filter: (district = 'California'::text)
(6 Zeilen)Was uns normale Indizes bieten:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows) Scan address wurde durch einen Indexscan ersetzt idx_address1, und dann wurde ein Heap gescannt address.
Da dies eine häufige Anfrage ist und optimiert werden muss, können wir einen partiellen Index verwenden, der nur die Adresszeilen indiziert, bei denen der Bezirk ‘Kalifornien’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='Kalifornien';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'Kalifornien';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12,38..28,96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0,00..14,99 rows=599 width=34)
-> Hash (cost=12,27..12,27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0,14..12,27 rows=9 width=4)
(5 rows) Jetzt liest die Anfrage nur idx_address2 und berührt nicht die Tabelle address.
Verwendung von Mehrwertindizes (Multi-Value Indexes)
Einige Spalten, die indiziert werden sollen, enthalten möglicherweise keinen Skalar-Datentyp. Spaltentypen wie jsonb, arrays und tsvector können zusammengesetzte oder mehrere Werte enthalten. Wenn Sie solche Spalten indizieren müssen, müssen Sie normalerweise nach allen einzelnen Werten in diesen Spalten suchen.
Lassen Sie uns die Titel aller Filme finden, die Szenen aus misslungenen Dublierungen enthalten. In der Tabelle film gibt es eine Textspalte mit dem Namen special_features. Wenn der Film dieses „besondere Merkmal“ hat, enthält die Spalte ein Element in Form eines Textarrays Hinter den Kulissen. Um alle diese Filme zu finden, müssen wir alle Reihen mit „Behind The Scenes“ auswählen, wenn beliebige Werte im Array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Eingabebetreiber (Containment-Operator) @> prüft, ob der rechte Teil eine Teilmenge des linken Teils ist.
Abfrageplan:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Der die vollständige Scanfunktion des Heaps mit einer Kosten von 67 anfordert.
Lassen Sie uns sehen, ob uns ein gewöhnlicher B-Baum-Index hilft:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Der Index wurde nicht einmal in Betracht gezogen. Der B-Baum-Index ahnt nicht einmal die Existenz einzelner Elemente in den indizierten Werten.
Wir benötigen einen GIN-Index.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)Der GIN-Index unterstützt die Zuordnung einzelner Werte zu indizierten zusammengesetzten Werten, wodurch die Kosten des Abfrageplans um mehr als die Hälfte gesenkt werden.
Doppelte Indizes entfernen
Indizes sammeln sich im Laufe der Zeit an, und manchmal kann ein neuer Index dieselbe Definition enthalten wie einer der vorherigen. Für menschenlesbare SQL-Definitionen von Indizes kann die Katalogansicht pg_indexes. Sie können auch leicht identische Definitionen finden:
Wählen Sie array_agg(indexname) AS indexes, ersetzen Sie indexdef, indexname, '' AS defn
AUS pg_indexes
GRUPPIEREN NACH defn
HAVING count(*) > 1;
Und hier ist das Ergebnis, wenn es auf der Standarddatenbank pagila ausgeführt wird:
pagila=# WÄHLEN Sie array_agg(indexname) AS indexes, ersetzen Sie indexdef, indexname, '' AS defn
pagila-# AUS pg_indexes
pagila-# GRUPPIEREN NACH defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | ERSTELLEN SIE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | ERSTELLEN SIE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | ERSTELLEN SIE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | ERSTELLEN SIE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | ERSTELLEN SIE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | ERSTELLEN SIE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 Zeilen)
Superset-Indizes
Es kann vorkommen, dass Sie viele Indizes haben, von denen einer ein Superset von Spalten indexiert, die andere Indizes indexieren. Dies kann sowohl erwünscht als auch unerwünscht sein – das Superset kann lediglich durch Indizes gescannt werden, was gut ist, aber es kann auch zu einem übermäßigen Platzbedarf führen, oder die Abfrage, für die dieses Superset zur Optimierung gedacht war, wird möglicherweise nicht mehr verwendet.
Wenn Sie die Identifizierung solcher Indizes automatisieren müssen, können Sie mit aus der Tabelle pg_catalog.
Nicht verwendete Indizes
Mit der Entwicklung von Anwendungen, die Datenbanken verwenden, entwickeln sich auch die Abfragen, die sie nutzen. Indizes, die zuvor hinzugefügt wurden, können von keiner Abfrage mehr genutzt werden. Bei jedem Scan des Index wird dieser vom Statistiker markiert, und im Sichtfeld des Systemkatalogs pg_stat_user_indexes kann der Wert angesehen werden, idx_scan, der einen kumulativen Zähler darstellt. Die Verfolgung dieses Wertes über einen bestimmten Zeitraum (sagen wir, einen Monat) gibt einen guten Überblick darüber, welche Indizes nicht verwendet werden und gelöscht werden können.
Hier ist die Anfrage zum Abrufen der aktuellen Abfragescanner für alle Indizes im Schema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
mit Ausgabe wie folgt:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 Zeilen)Wiederherstellung von Indizes mit weniger Sperren
Indizes müssen häufig wiederhergestellt werden, zum Beispiel wenn sie anwachsen, und die Wiederherstellung kann das Scannen beschleunigen. Auch können Indizes beschädigt werden. Änderungen an den Indexparametern können ebenfalls eine Wiederherstellung erforderlich machen.
Wir aktivieren die parallele Erstellung von Indizes
In PostgreSQL 11 ist die Erstellung eines B-Tree-Indexes konkurrenzfähig. Um den Prozess zu beschleunigen, können mehrere parallel arbeitende Worker genutzt werden. Stellen Sie jedoch sicher, dass diese Konfigurationsparameter korrekt gesetzt sind:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Die Standardwerte sind zu niedrig. Idealerweise sollten diese Zahlen mit der Anzahl der CPU-Kerne erhöht werden. Weitere Informationen finden Sie in .
Hintergrundindexerstellung
Sie können einen Index im Hintergrund erstellen, indem Sie die Option CONCURRENTLY verwendet. Die Verwendung von Optionen hilft, das Verhalten der Befehle anzupassen. CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDieses Indexerstellungsverfahren unterscheidet sich von der herkömmlichen Methode, da es keine Sperrung der Tabelle erfordert und somit keine Schreibvorgänge blockiert. Auf der anderen Seite benötigt es mehr Zeit und Ressourcen.
Postgres bietet zahlreiche flexible Möglichkeiten zur Erstellung von Indizes und zur Lösung individueller Fälle sowie Methoden zur Verwaltung der Datenbank im Falle eines exponentiellen Wachstums Ihrer Anwendung. Wir hoffen, dass Ihnen diese Tipps helfen, Ihre Abfragen zu beschleunigen und Ihre Datenbank für das Wachstum vorzubereiten.
Quelle: habr.com
