Dans le monde Postgres, les index sont essentiels pour une navigation efficace dans le stockage de la base de données (appelée "heap"). Postgres ne prend pas en charge le clustering pour cela, et l'architecture MVCC vous amène à vous retrouver avec de nombreuses versions du même tuple. Par conséquent, il est très important de pouvoir créer et maintenir des index efficaces pour prendre en charge les applications.
Voici quelques conseils pour optimiser et améliorer l'utilisation des index.
Remarque : les requêtes présentées ci-dessous fonctionnent sur un fichier non modifié. .
Utilisation des index de recouvrement
Examinons une demande d'extraction d'adresses e-mail pour les utilisateurs inactifs. Tableau customer il y a une colonne active, et la requête est simple :
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)
La requête invoque la séquence d'analyse complète de la table customer. Créons un index sur une colonne 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)
Cela a aidé, l'analyse ultérieure s'est transformée en "index scan". Cela signifie que Postgres va scanner l'index "idx_cust1", puis continuez à chercher dans le tas de la table pour lire les valeurs des autres colonnes (dans ce cas, la colonne email) dont la requête a besoin.
Les index de couverture sont introduits dans PostgreSQL 11. Ils vous permettent d'inclure une ou plusieurs colonnes supplémentaires dans l'index lui-même - leurs valeurs sont stockées dans le magasin de données d'index.
Si nous profitions de cette fonctionnalité et ajoutions la valeur de l'e-mail dans l'index, Postgres n'aurait pas besoin de rechercher la valeur dans le tas de la table. email. Voyons si cela fonctionnera :
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' nous indique que la requête n'a plus besoin que d'un index, ce qui permet d'éviter toutes les E/S disque pour lire le tas de la table.
Les index de couverture ne sont actuellement disponibles que pour les arbres B. Cependant, dans ce cas, l'effort de maintenance sera plus important.
Utilisation d'index partiels
Les index partiels n'indexent qu'un sous-ensemble des lignes d'une table. Cela permet d'économiser la taille des index et d'accélérer les analyses.
Supposons que nous souhaitions obtenir la liste des adresses e-mail de nos clients en Californie. La requête ressemblera à ceci :
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)Quels indices ordinaires nous donneront :
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 a été remplacé par balayage d'index idx_address1puis scanné le tas address.
Comme il s'agit d'une requête fréquente et qu'elle doit être optimisée, 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';
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)
Maintenant, la requête ne lit que idx_address2 et ne touche pas la table address.
Utilisation d'index multivaleurs
Certaines colonnes à indexer peuvent ne pas contenir de type de données scalaire. Types de colonnes comme jsonb, arrays и tsvector contiennent des valeurs composées ou multiples. Si vous devez indexer de telles colonnes, vous devez généralement effectuer une recherche dans toutes les valeurs individuelles de ces colonnes.
Essayons de trouver les titres de tous les films contenant des coupures de prises infructueuses. Tableau film il y a une colonne de texte appelée special_features. Si le film a cette "propriété spéciale", alors la colonne contient l'élément en tant que tableau de texte Behind The Scenes. Pour rechercher tous ces films, nous devons sélectionner toutes les lignes avec "Behind The Scenes" lorsque tout valeurs de tableau special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Opérateur d'imbrication @> vérifie si le côté droit est un sous-ensemble du côté gauche.
Plan de demande :
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)Ce qui demande une analyse complète du tas avec un coût de 67.
Voyons si un index B-tree régulier nous aide :
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)L'indice n'a même pas été pris en compte. L'index B-tree n'a pas connaissance de l'existence d'éléments individuels dans les valeurs indexées.
Nous avons besoin d'un indice GIN.
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)L'index GIN prend en charge le mappage des valeurs uniques par rapport aux valeurs composites indexées, ce qui entraîne un coût du plan de requête plus que divisé par deux.
Se débarrasser des index en double
Les index s'accumulent au fil du temps, et parfois un nouvel index peut contenir la même définition que l'un des précédents. Vous pouvez utiliser la vue du catalogue pour obtenir des définitions SQL lisibles par l'homme des index. pg_indexes. Vous pouvez également trouver facilement des définitions identiques :
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)
Index sur-ensemble
Il peut arriver que vous vous retrouviez avec de nombreux index, dont l'un indexe un sur-ensemble de colonnes qui indexent d'autres index. Cela peut être souhaitable ou non - le sur-ensemble peut entraîner des analyses d'index uniquement, ce qui est bien, mais cela peut prendre trop de place, ou la requête que le sur-ensemble était censé optimiser n'est plus utilisée.
Si vous avez besoin d'automatiser la définition de tels index, vous pouvez commencer par de la table pg_catalog.
Index inutilisés
À mesure que les applications qui utilisent des bases de données évoluent, les requêtes qu'elles utilisent évoluent également. Les index ajoutés précédemment ne peuvent plus être utilisés par aucune requête. Chaque fois qu'un index est scanné, 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. Le suivi de cette valeur sur une période de temps (disons un mois) donnera une bonne idée des index qui ne sont pas utilisés et qui pourraient être supprimés.
Voici une requête pour obtenir le nombre d'analyses actuelles de tous les index du schéma ‘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)Reconstruire des index avec moins de verrous
Les index doivent souvent être reconstruits, par exemple lorsqu'ils deviennent gonflés, et la reconstruction peut accélérer l'analyse. Les index peuvent également être corrompus. La modification des paramètres d'index peut également nécessiter sa reconstruction.
Activer la création d'index parallèles
Dans PostgreSQL 11, la création d'un index B-Tree est simultanée. Pour accélérer le processus de création, plusieurs travailleurs parallèles peuvent être utilisés. Cependant, assurez-vous que ces options de configuration sont correctement définies :
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Les valeurs par défaut sont trop petites. Idéalement, ces chiffres devraient augmenter avec le nombre de cœurs de processeur. En savoir plus dans .
Création d'un index d'arrière-plan
Vous pouvez créer un index en arrière-plan en utilisant l'option CONCURRENTLY les équipes CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXCette procédure de création d'index diffère de la procédure habituelle en ce qu'elle ne nécessite pas de verrou sur la table, et donc ne bloque pas les opérations d'écriture. En revanche, cela prend plus de temps et consomme plus de ressources.
Postgres offre une grande flexibilité pour créer des index et des moyens de résoudre tous les cas particuliers, ainsi que des moyens de gérer la base de données au cas où votre application se développerait de manière explosive. Nous espérons que ces conseils vous aideront à accélérer vos requêtes et à préparer votre base de données à évoluer.
Source: habr.com