ProHoster > Blog > Administration > Wir nutzen alle Möglichkeiten von Indizes in PostgreSQL

Wir nutzen alle Möglichkeiten von Indizes in PostgreSQL

Wir nutzen alle Möglichkeiten von Indizes in PostgreSQL
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 Datenbankinstanz von pagila.

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 pg_index 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 Dokumentation.

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 INDEX

Dieses 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

ZuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz kaufen, VPS VDS Server đŸ”„ ZuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz kaufen, VPS VDS Server - ProHoster