In der Postgres-Welt sind Indizes für die effiziente Navigation im Datenbankspeicher („Heap“) unerlässlich. Postgres unterstützt dafür kein Clustering und die MVCC-Architektur führt dazu, dass Sie am Ende viele Versionen desselben Tupels erhalten. Daher ist es sehr wichtig, effiziente Indizes zur Unterstützung von Anwendungen erstellen und verwalten zu können.
Hier finden Sie einige Tipps zur Optimierung und Verbesserung der Verwendung von Indizes.
Hinweis: Die unten gezeigten Abfragen funktionieren mit einer unveränderten Version .
Verwendung abdeckender Indizes
Schauen wir uns eine Anfrage zum Extrahieren von E-Mail-Adressen für inaktive Benutzer an. Tisch customer Es gibt eine Spalte active, und die Abfrage ist 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)
Die Abfrage ruft die vollständige Tabellenscansequenz auf customer. Lassen Sie uns einen Index für eine Spalte erstellen active:
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 rows)
Es hat geholfen, der anschließende Scan ergab „index scan". Das bedeutet, dass Postgres den Index scannt.idx_cust1", und durchsuchen Sie dann den Tabellenheap weiter, um die Werte anderer Spalten (in diesem Fall der Spalte) zu lesen email), die die Abfrage benötigt.
Covering-Indizes werden in PostgreSQL 11 eingeführt. Sie ermöglichen es Ihnen, eine oder mehrere zusätzliche Spalten in den Index selbst aufzunehmen – ihre Werte werden im Indexdatenspeicher gespeichert.
Wenn wir diese Funktion nutzen und den E-Mail-Wert in den Index einfügen würden, müsste Postgres den Tabellenheap nicht nach dem Wert durchsuchen. email. Mal sehen, ob das klappt:
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 rows)
«Index Only Scan' sagt uns, dass die Abfrage jetzt nur noch einen Index benötigt, wodurch alle Festplatten-E/A zum Lesen des Tabellenheaps vermieden werden.
Covering-Indizes sind derzeit nur für B-Bäume verfügbar. Allerdings ist in diesem Fall der Wartungsaufwand höher.
Teilindizes verwenden
Teilindizes indizieren nur eine Teilmenge der Zeilen in einer Tabelle. Dies spart die Größe der Indizes und beschleunigt die Scans.
Nehmen wir an, wir möchten eine Liste der E-Mail-Adressen unserer Kunden in Kalifornien erhalten. Die Anfrage sieht folgendermaßen aus:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
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=15.65..32.22 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=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)Was uns gewöhnliche Indizes liefern:
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)
Scannen address wurde durch Index-Scan ersetzt idx_address1und dann den Heap gescannt address.
Da dies eine häufige Abfrage ist und optimiert werden muss, können wir einen Teilindex verwenden, der nur die Zeilen mit Adressen indiziert, in denen der Bezirk liegt ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
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.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 Abfrage nur idx_address2 und berührt den Tisch nicht address.
Verwendung mehrwertiger Indizes
Einige zu indizierende Spalten enthalten möglicherweise keinen skalaren Datentyp. Spaltentypen wie jsonb, arrays и tsvector zusammengesetzte oder mehrere Werte enthalten. Wenn Sie solche Spalten indizieren müssen, müssen Sie normalerweise alle einzelnen Werte in diesen Spalten durchsuchen.
Versuchen wir, die Titel aller Filme zu finden, die Ausschnitte aus erfolglosen Einstellungen enthalten. Tisch film Es gibt eine Textspalte namens special_features. Wenn der Film über diese „spezielle Eigenschaft“ verfügt, enthält die Spalte das Element als Textarray Behind The Scenes. Um nach allen solchen Filmen zu suchen, müssen wir alle Zeilen mit „Behind The Scenes“ auswählen jeder Array-Werte special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Verschachtelungsbetreiber @> prüft, ob die rechte Seite eine Teilmenge der linken Seite ist.
Anfrageplan:
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)Dies erfordert einen vollständigen Heap-Scan mit Kosten von 67.
Mal sehen, ob uns ein regulärer B-Tree-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 berücksichtigt. Der B-Tree-Index ist sich der Existenz einzelner Elemente in den indizierten Werten nicht bewusst.
Wir brauchen 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, was zu mehr als der Hälfte der Kosten für den Abfrageplan führt.
Doppelte Indizes entfernen
Indizes sammeln sich im Laufe der Zeit an und manchmal enthält ein neuer Index dieselbe Definition wie einer der vorherigen. Sie können die Katalogansicht verwenden, um für Menschen lesbare SQL-Definitionen von Indizes abzurufen. pg_indexes. Sie können auch leicht identische Definitionen finden:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Superset-Indizes
Es kann vorkommen, dass Sie am Ende viele Indizes haben, von denen einer eine Obermenge von Spalten indiziert, die andere Indizes indizieren. Dies kann erwünscht sein oder auch nicht – die Obermenge kann zu reinen Index-Scans führen, was zwar gut ist, aber möglicherweise zu viel Platz beansprucht, oder die Abfrage, die die Obermenge optimieren sollte, wird nicht mehr verwendet.
Wenn Sie die Definition solcher Indizes automatisieren müssen, können Sie damit beginnen vom Tisch pg_catalog.
Unbenutzte Indizes
Mit der Weiterentwicklung von Anwendungen, die Datenbanken verwenden, entwickeln sich auch die von ihnen verwendeten Abfragen weiter. Zuvor hinzugefügte Indizes dürfen von keiner Abfrage mehr verwendet werden. Jedes Mal, wenn ein Index gescannt wird, wird er vom Statistikmanager und in der Systemkatalogansicht markiert pg_stat_user_indexes Sie können den Wert sehen idx_scan, was ein kumulativer Zähler ist. Wenn Sie diesen Wert über einen bestimmten Zeitraum (z. B. einen Monat) verfolgen, erhalten Sie eine gute Vorstellung davon, welche Indizes nicht verwendet werden und gelöscht werden könnten.
Hier ist eine Abfrage, um die aktuelle Scananzahl aller Indizes im Schema abzurufen ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
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 rows)Neuerstellung von Indizes mit weniger Sperren
Indizes müssen oft neu erstellt werden, beispielsweise wenn sie überlastet sind, und ein Neuaufbau kann den Scan beschleunigen. Auch Indizes können beschädigt werden. Das Ändern der Indexparameter erfordert möglicherweise auch eine Neuerstellung.
Aktivieren Sie die parallele Indexerstellung
In PostgreSQL 11 erfolgt die Erstellung eines B-Tree-Index gleichzeitig. Um den Erstellungsprozess zu beschleunigen, können mehrere parallele Worker eingesetzt werden. Stellen Sie jedoch sicher, dass diese Konfigurationsoptionen richtig eingestellt sind:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Die Standardwerte sind zu klein. Idealerweise sollten diese Zahlen zusammen mit der Anzahl der Prozessorkerne steigen. Lesen Sie mehr in .
Hintergrundindexerstellung
Mit der Option können Sie im Hintergrund einen Index erstellen CONCURRENTLY команды CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDieses Verfahren zur Indexerstellung unterscheidet sich vom üblichen Verfahren dadurch, dass es keine Sperre für die Tabelle erfordert und daher keine Schreibvorgänge blockiert. Andererseits nimmt es mehr Zeit in Anspruch und verbraucht mehr Ressourcen.
Postgres bietet viel Flexibilität beim Erstellen von Indizes und Möglichkeiten zur Lösung spezieller Fälle sowie Möglichkeiten zur Verwaltung der Datenbank für den Fall, dass Ihre Anwendung explosionsartig wächst. Wir hoffen, dass diese Tipps Ihnen dabei helfen, Ihre Abfragen schnell durchzuführen und Ihre Datenbank skalierbar zu machen.
Source: habr.com