In der Welt von Postgres sind Indizes äußerst wichtig für eine effiziente Navigation im Datenbankspeicher (dies wird als „Heap“ bezeichnet). Postgres unterstützt dafür keine Clusterung, und die MVCC-Architektur führt dazu, dass viele Versionen derselben Tupel angesammelt werden. Daher ist es entscheidend, effektive Indizes zur Unterstützung von Anwendungen zu erstellen und zu pflegen.
Ich möchte Ihnen einige Tipps zur Optimierung und Verbesserung der Nutzung von Indizes vorstellen.
Hinweis: Die unten gezeigten Abfragen funktionieren auf einer unmodifizierten .
Nutzung von abdeckenden Indizes (Covering Indexes)
Lassen Sie uns eine Abfrage zur Extrahierung von E-Mail-Adressen für inaktive Benutzer betrachten. In der Tabelle customer gibt es eine Spalte active, 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 Tabellen-Scan aufgerufen. customerLassen Sie uns einen Index für die 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 folgende Scan verwandelte sich in einen „index scan“. Das bedeutet, dass Postgres den Index „idx_cust1“ scannen wird und dann mit der Suche im Heap der Tabelle fortfährt, um die Werte anderer Spalten (in diesem Fall die Spalte email) zu lesen, die für die Abfrage benötigt werden.
In PostgreSQL 11 wurden abdeckende Indizes eingeführt. Sie ermöglichen es, eine oder mehrere zusätzliche Spalten in den Index aufzunehmen – ihre Werte werden im Datenspeicher des Index gespeichert.
Wenn wir diese Möglichkeit genutzt und den Wert der E-Mail in den Index aufgenommen hätten, wäre es für Postgres nicht notwendig gewesen, im Heap der Tabelle nach dem Wert zu suchen. emailLassen Sie uns sehen, ob das funktionieren wird:
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 für die Abfrage jetzt nur noch ein Index erforderlich ist, was hilft, alle Festplatten-Ein-/Ausgabeoperationen zum Lesen von Tabellenhaufen zu vermeiden.
Heute sind abdeckende Indizes nur für B-Bäume verfügbar. Allerdings werden in diesem Fall die Wartungsaufwände höher sein.
Verwendung von partiellen Indizes
Partielle Indizes indizieren nur eine Teilmenge der Zeilen in einer Tabelle. Dies hilft, den Platzbedarf der Indizes zu reduzieren und die Abfragegeschwindigkeit zu erhöhen.
Angenommen, wir müssen eine Liste von E-Mail-Adressen unserer Kunden aus Kalifornien abrufen. Die Abfrage würde so aussehen:
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 das Scannen beider verbundenen Tabellen umfasst:
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 (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 normale Indizes bringen:
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';
ABFRAGEPLAN
---------------------------------------------------------------------------------------
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 das Scannen des Index ersetzt idx_address1, und dann wurde der Haufen gescannt address.
Da dies eine häufige Abfrage ist und optimiert werden muss, können wir einen partiellen Index verwenden, der nur die Zeilen mit Adressen indiziert, in denen der Bezirk ‘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';
ABFRAGEPLAN
------------------------------------------------------------------------------------------------
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 noch idx_address2 und berührt die Tabelle nicht address.
Die Verwendung von mehrwertigen Indizes (Multi-Value Indexes)
Einige Spalten, die indiziert werden müssen, können keinen Skalar-Datentyp enthalten. Spaltentypen wie jsonb, Arrays und tsvector können zusammengesetzte oder mehrere Werte enthalten. Wenn Sie solche Spalten indizieren müssen, müssen Sie meistens nach allen einzelnen Werten in diesen Spalten suchen.
Lassen Sie uns versuchen, die Titel aller Filme zu finden, die Schnitte aus gescheiterten Aufnahmen enthalten. In der Tabelle film gibt es eine Textspalte mit dem Namen special_features. Wenn der Film diese "besondere Eigenschaft" hat, wird in der Spalte ein Element in Form eines Textarrays gespeichert Behind The Scenes. Um alle solche Filme zu finden, müssen wir alle Zeilen mit "Behind The Scenes" bei beliebigen Werten des Arrays auswählen special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Der Verschachtelungsoperator (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 Stapelscannung mit Kosten von 67 anfordert.
Lassen Sie uns sehen, ob uns ein normaler 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 nichts von der 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.
Wir vermeiden die Duplizierung von Indizes.
Indizes sammeln sich im Laufe der Zeit, und manchmal kann ein neuer Index dieselbe Definition enthalten wie einer der vorherigen. Um lesbare SQL-Definitionen von Indizes zu erhalten, kann die Katalogansicht verwendet werden 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;
Und hier ist das Ergebnis, wenn es auf der Standardpagila-Datenbank ausgeführt wird:
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 Zeilen)
Superset-Indizes
Es kann vorkommen, dass Sie viele Indizes haben, von denen einer eine Obermenge von Spalten indiziert, die andere Indizes indizieren. Dies kann sowohl wünschenswert als auch unerwünscht sein – eine Obermenge kann zu einer bloßen Indexauswertung führen, was gut ist, aber gleichzeitig möglicherweise zu viel Speicherplatz beansprucht oder die Abfrage, für die diese Obermenge optimiert wurde, bereits nicht mehr verwendet wird.
Wenn Sie solche Indizes automatisieren müssen, können Sie mit aus der Tabelle pg_catalog.
Nicht verwendete Indizes
Mit der Entwicklung von Anwendungen, die Datenbanken nutzen, entwickeln sich auch die Abfragen, die sie verwenden. Zuvor hinzugefügte Indizes können von keiner Abfrage mehr verwendet werden. Bei jedem Scannen des Index wird dieser vom Statistik-Dispatcher markiert, und im Systemkatalogansicht pg_stat_user_indexes kann der Wert angesehen werden idx_scan, das ein kumulativer Zähler ist. 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 Abfrage zur Abholung der aktuellen Scanzähler aller Indizes im Schema 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
mit einer Ausgabe wie dieser:
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)Neuaufbau von Indizes mit weniger Sperren
Häufig müssen Indizes neu erstellt werden, zum Beispiel, wenn sie sich vergrößern, und ein Neuaufbau kann das Scannen beschleunigen. Auch können Indizes beschädigt werden. Eine Änderung der Indexparameter kann ebenfalls eine Neuerstellung erforderlich machen.
Aktivieren von paralleler Erstellung von Indizes
In PostgreSQL 11 ist die Erstellung eines B-Tree-Index wettbewerbsfähig. Um den Erstellungsprozess zu beschleunigen, können mehrere parallel arbeitende Worker verwendet werden. Stellen Sie jedoch sicher, dass diese Konfigurationsparameter richtig eingestellt sind:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Die Standardwerte sind zu niedrig. Idealerweise sollten diese Zahlen zusammen mit der Anzahl der Prozessorkerne erhöht werden. Weitere Informationen finden Sie in der .
Hintergrund Erstellung von Indizes
Sie können einen Index im Hintergrund erstellen, indem Sie den Parameter CONCURRENTLY Befehle CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDieses Verfahren zur Erstellung eines Index unterscheidet sich von der normalen, da es keine Sperrung der Tabelle erfordert und somit auch keine Schreibvorgänge blockiert. Auf der anderen Seite dauert es länger und benötigt mehr Ressourcen.
Postgres bietet viele flexible Möglichkeiten zur Erstellung von Indizes und zur Lösung spezifischer Probleme. Außerdem bietet es Möglichkeiten zur Verwaltung der Datenbank im Fall einer explosiven Wachstumsphase Ihrer Anwendung. Wir hoffen, dass diese Tipps Ihnen helfen werden, Ihre Abfragen zu beschleunigen und die Datenbank für das Skalieren bereit zu machen.
Quelle: habr.com
