Utiliser toutes les capacités des index dans PostgreSQL

Dans le monde de Postgres, les index sont essentiels pour naviguer efficacement dans le stockage de la base de données (appelé « heap »). Postgres ne prend pas en charge la clustering pour cela, et l'architecture MVCC fait que de nombreuses versions d'une même ligne s'accumulent. Il est donc crucial de savoir comment créer et entretenir des index efficaces pour soutenir les applications.

Je vous propose quelques conseils pour optimiser et améliorer l'utilisation des index.

Remarque : les requêtes ci-dessous fonctionnent sur un exemple de base de données pagila.

Utilisation des index couverts (Covering Indexes)

Prenons une requête pour extraire les adresses électroniques des utilisateurs inactifs. Dans la table customer il y a une colonne active, et la requête reste simple :

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        PLAN DE REQUÊTE
-----------------------------------------------------------
 Scan séquentiel sur customer  (coût=0.00..16.49 lignes=15 largeur=32)
   Filtre : (active = 0)
(2 lignes)

La requête effectue une analyse séquentielle complète de la table. customerCréons un index pour la colonne active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 PLAN DE REQUÊTE
-----------------------------------------------------------------------------
 Scan d'index utilisant idx_cust1 sur customer  (coût=0.28..12.29 lignes=15 largeur=32)
   Condition d'index : (active = 0)
(2 lignes)

Cela a aidé, l'analyse suivante est devenue un «scan d'index». Cela signifie que Postgres va examiner l'index «idx_cust1», puis continuer à chercher dans le heap de la table pour lire les valeurs d'autres colonnes (dans ce cas, la colonne email), nécessaires à la requête.

Dans PostgreSQL 11, des index couverts ont été introduits. Ils permettent d'inclure dans l'index une ou plusieurs colonnes supplémentaires — leurs valeurs sont stockées dans le stockage des données de l'index.

Si nous utilisions cette fonctionnalité et ajoutions la valeur de l'e-mail à l'intérieur de l'index, Postgres n'aurait pas besoin de chercher dans le heap de la table pour obtenir la valeur. emailVoyons si cela fonctionnera :

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    PLAN DE REQUÊTE
----------------------------------------------------------------------------------
 Scan seulement d'index utilisant idx_cust2 sur customer  (coût=0.28..12.29 lignes=15 largeur=32)
   Condition d'index : (active = 0)
(2 lignes)

«Scan seulement d'index» nous indique que la requête nécessite maintenant uniquement l'index, ce qui permet d'éviter toutes les opérations d'entrée/sortie disque pour lire le heap de la table.

Aujourd'hui, les index couvrants ne sont disponibles que pour les arbres B. Cependant, dans ce cas, les efforts de maintenance seront plus élevés.

Utilisation des index partiels

Les index partiels n'indexent qu'un sous-ensemble des lignes d'une table. Cela permet d'économiser de l'espace pour les index et d'effectuer des scans plus rapidement.

Supposons que nous devons obtenir la liste des adresses e-mail de nos clients en Californie. La requête sera telle que :

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
qui a un plan de requête impliquant le scan des deux tables jointes :
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              PLAN DE REQUÊTE
----------------------------------------------------------------------
 Jointure Hachée  (coût=15.65..32.22 lignes=9 largeur=32)
   Cond. Hachée : (c.address_id = a.address_id)
   ->  Scan Séquentiel sur customer c  (coût=0.00..14.99 lignes=599 largeur=34)
   ->  Haché  (coût=15.54..15.54 lignes=9 largeur=4)
         ->  Scan Séquentiel sur address a  (coût=0.00..15.54 lignes=9 largeur=4)
               Filtre : (district = 'California'::text)
(6 lignes)

Que nous donneront les index classiques :

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';
                                      PLAN DE REQUÊTE
---------------------------------------------------------------------------------------
 Jointure Hachée  (coût=12.98..29.55 lignes=9 largeur=32)
   Cond. Hachée : (c.address_id = a.address_id)
   ->  Scan Séquentiel sur customer c  (coût=0.00..14.99 lignes=599 largeur=34)
   ->  Haché  (coût=12.87..12.87 lignes=9 largeur=4)
         ->  Scan de Tas Bitmap sur address a  (coût=4.34..12.87 lignes=9 largeur=4)
               Condition de Vérification : (district = 'California'::text)
               ->  Scan d'Index Bitmap sur idx_address1  (coût=0.00..4.34 lignes=9 largeur=0)
                     Cond. d'Index : (district = 'California'::text)
(8 lignes)

Scan address a été remplacé par le scan de l'index idx_address1, puis une table a été scanné address.

Comme c'est une requête fréquente qui nécessite une optimisation, nous pouvons utiliser un index partiel qui n'indexe que les lignes avec des adresses dans lesquelles le district ‘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';
                                           PLAN DE REQUÊTE
------------------------------------------------------------------------------------------------
 Jointure Hachée  (coût=12.38..28.96 lignes=9 largeur=32)
   Cond. Hachée : (c.address_id = a.address_id)
   ->  Scan Séquentiel sur customer c  (coût=0.00..14.99 lignes=599 largeur=34)
   ->  Haché  (coût=12.27..12.27 lignes=9 largeur=4)
         ->  Scan Index Seul utilisant idx_address2 sur address a  (coût=0.14..12.27 lignes=9 largeur=4)
(5 lignes)

Maintenant, la requête lit seulement idx_address2 et n'affecte pas la table address.

Utilisation des index à valeurs multiples (Multi-Value Indexes)

Certain columns that need to be indexed may not contain scalar data types. Column types like jsonb, arrays et tsvector can contain composite or multiple values. If you need to index such columns, you usually have to search through all individual values in these columns.

Let's try to find the titles of all films containing cuts from failed takes. In the table film there is a text column called special_features. If a film has this 'special feature', then the column contains an element in the form of a text array Behind The Scenes. To search for all such films, we need to select all rows with 'Behind The Scenes' for any array values special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

The containment operator @> checks whether the right side is a subset of the left side.

Query plan:

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)

Which asks for a full heap scan at a cost of 67.

Let's see if a regular B-tree index helps us:

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)

The index wasn't even considered. The B-tree index does not infer the existence of individual elements in indexed values.

We need a 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)

The GIN index supports matching individual values to indexed composite values, resulting in a query plan cost reduction of more than half.

Let’s eliminate index duplication.

Les index s'accumulent avec le temps, et parfois un nouvel index peut contenir la même définition qu'un des précédents. Pour obtenir des définitions d'index lisibles par l'homme, vous pouvez utiliser la vue système. pg_indexes. Vous pourrez également facilement trouver des définitions identiques :

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
Et voici le résultat lorsqu'il est exécuté sur la base de données pagila par défaut :
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 lignes)

Index de sur-ensembles (Superset Indexes)

Il se peut que vous accumuliez de nombreux index, dont l'un indexe un sur-ensemble de colonnes qui sont déjà indexées par d'autres index. Cela peut être souhaitable ou non — un sur-ensemble peut mener à un scan uniquement par les index, ce qui est bien, mais il peut aussi prendre trop de place, ou bien la requête que cet ensemble devait optimiser n'est plus utilisée.

Si vous devez automatiser la détermination de tels index, vous pouvez commencer par pg_index de la table pg_catalog.

Index non utilisés

Avec l'évolution des applications qui utilisent des bases de données, les requêtes qu'elles utilisent évoluent également. Les index ajoutés précédemment peuvent ne plus être utilisés par aucune requête. À chaque scan d'index, il est marqué par le gestionnaire de statistiques, et dans la vue du catalogue système pg_stat_user_indexes vous pouvez voir la valeur idx_scan, qui est un compteur cumulatif. Suivre cette valeur sur une période donnée (disons, un mois) donnera une bonne idée des index qui ne sont pas utilisés et qui peuvent être supprimés.

Voici la requête pour obtenir les compteurs de scan actuels de tous les index dans le schéma 'public':

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
avec une sortie comme ceci :
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 lignes)

Recréer des index avec moins de blocages

Il arrive souvent qu'il faille recréer des index, par exemple lorsqu'ils gonflent en taille, et leur recréation peut accélérer le scan. De plus, les index peuvent être corrompus. Modifier les paramètres d'un index peut également nécessiter sa recréation.

Activer la création d'index en parallèle

Dans PostgreSQL 11, la création d'un index B-Tree est concurrente. Pour accélérer le processus de création, plusieurs travailleurs peuvent être utilisés en parallèle. Assurez-vous cependant que ces paramètres de configuration sont correctement définis :

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Les valeurs par défaut sont trop faibles. Idéalement, ces chiffres devraient augmenter avec le nombre de cœurs de processeur. Lisez-en plus dans documentation.

Création d'index en arrière-plan

Vous pouvez créer un index en arrière-plan en utilisant le paramètre CONCURRENTLY des commandes CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Cette procédure de création d'index se distingue de la normale en ce sens qu'elle ne nécessite pas de verrouillage de la table, ce qui signifie qu'elle ne bloque pas les opérations d'écriture. D'un autre côté, elle prend plus de temps et consomme plus de ressources.

Postgres offre de nombreuses options flexibles pour la création d'index et des solutions à tous les cas particuliers, ainsi que des moyens de gérer la base de données en cas de croissance explosive de votre application. Nous espérons que ces conseils vous aideront à rendre vos requêtes rapides et votre base prête à évoluer.

Source : habr.com