Dans le monde Postgres, les index sont cruciaux pour naviguer efficacement dans un référentiel de base de données (appelé tas, tas). Postgres ne prend pas en charge le clustering pour cela, et l'architecture MVCC vous fait accumuler 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 une base de données exemple pagila non modifiée.Utilisation des index de couverture
Examinons la demande de récupération des adresses e-mail des utilisateurs inactifs. La table
customer a une colonne
active et la demande est simple:
pagila=
La requête appelle la séquence complète de l'analyse de la table
customer . Créons un index pour la colonne
active :
pagila=
Cela a aidé, l'analyse suivante s'est transformée en une "
index scan ". Cela signifie que Postgres analysera l'index
idx_cust1 , puis continuera à rechercher le tas de la table pour lire les valeurs des autres colonnes (dans ce cas, la colonne
email -
email ) dont la requête a besoin.
PostgreSQL 11 a introduit les index de couverture. 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 avons utilisé cette fonctionnalité et ajouté une valeur de courrier électronique à l'intérieur de l'index, Postgres n'aurait pas besoin de rechercher la valeur de
email dans le tas de table. Voyons voir si cela fonctionne:
pagila=
"
Index Only Scan " nous indique que la requête n'a désormais besoin que d'un seul index, ce qui évite à toutes les E / S disque de lire le tas de table.
Aujourd'hui, les indices de couverture ne sont disponibles que pour les arbres B. Cependant, dans ce cas, les efforts d'escorte seront plus importants.
Utilisation d'index partiels
Les index partiels indexent uniquement un sous-ensemble des lignes d'une table. Cela permet d'économiser la taille des index et des analyses plus rapides.
Supposons que nous ayons besoin d'obtenir une liste d'adresses e-mail de nos clients californiens. La demande sera comme 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=
Quels indices ordinaires nous donneront:
pagila=
L'analyse d'
address été remplacée par l'
idx_address1 index
idx_address1 , puis le
idx_address1 address été analysé.
Comme il s'agit d'une requête fréquente et doit être optimisée, nous pouvons utiliser un index partiel qui indexe uniquement les lignes avec des adresses dans lesquelles la région
'California' :
pagila=
Désormais, la demande ne lit que
idx_address2 et ne touche pas la table d'
address .
Utilisation d'index multi-valeurs
Certaines colonnes qui doivent être indexées peuvent ne pas contenir de type de données scalaire.
jsonb types de
jsonb tels que
jsonb ,
arrays et
tsvector contiennent plusieurs ou plusieurs valeurs. Si vous devez indexer de telles colonnes, vous devez généralement rechercher toutes les valeurs individuelles dans ces colonnes.
Essayons de trouver les noms de tous les films contenant des coupures de prises infructueuses. La table de
film a une colonne de texte appelée
special_features . Si le film possède cette «propriété spéciale», la colonne contient un élément sous la forme d'un tableau de texte
Behind The Scenes . Pour rechercher tous ces films, nous devons sélectionner toutes les lignes avec «Dans les coulisses» pour
toutes les valeurs du tableau
special_features :
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
L'opérateur de confinement
@> vérifie si le côté droit est un sous-ensemble du côté gauche.
Plan de demande:
pagila=
Qui demande une analyse complète du tas avec un coût de 67.
Voyons si l'index B-tree régulier nous aide:
pagila=
L'indice n'a même pas été pris en compte. L'index de l'arbre B n'est pas conscient de l'existence d'éléments individuels dans les valeurs indexées.
Nous avons besoin d'un index GIN.
pagila=
GIN-index prend en charge la comparaison des valeurs individuelles avec les valeurs composites indexées, par conséquent, le coût du plan de requête est réduit de plus de moitié.
Supprimer les index en double
Les index s'accumulent avec le temps, et parfois un nouvel index peut contenir la même définition que l'un des précédents. Pour obtenir des définitions SQL lisibles par l'homme des index, vous pouvez utiliser la vue catalogue
pg_indexes . Vous pouvez également retrouver facilement les mêmes définitions:
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=
Index de surensemble
Il peut arriver que vous accumuliez de nombreux index, dont l'un indexe un sous-ensemble des colonnes qui indexent d'autres index. Cela peut être souhaitable ou non - un sur-ensemble ne peut analyser que par index, ce qui est bien, mais il peut prendre trop de place, ou la requête pour laquelle ce sur-ensemble était destiné à être optimisé n'est plus utilisée.
Si vous avez besoin d'automatiser la définition de ces index, vous pouvez commencer avec
pg_index à partir de la table
pg_catalog .
Index inutilisés
À mesure que les applications qui utilisent des bases de données se développent, les requêtes qu'elles utilisent se développent également. Les index ajoutés précédemment ne peuvent plus être utilisés par aucune requête. Chaque fois que l'index est analysé, il est marqué par le gestionnaire de statistiques et dans la
pg_stat_user_indexes 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 peuvent être supprimés.
Voici une demande pour obtenir le nombre de scan actuel de tous les index dans le schéma
'public' :
SELECT relname, indexrelname, idx_scan FROM pg_catalog.pg_stat_user_indexes WHERE schemaname = 'public'; with output like this: pagila=
Recréer des index avec moins de verrous
Souvent, les index doivent être recréés, par exemple, lorsqu'ils sont gonflés en taille, et la recréation peut accélérer l'analyse. De plus, les indices peuvent être corrompus. La modification des paramètres d'index peut également nécessiter sa recréation.
Activer la création d'index parallèle
Dans PostgreSQL 11, la création d'un index B-Tree est compétitive. Pour accélérer le processus de création, plusieurs travailleurs parallèles peuvent être utilisés. Cependant, assurez-vous que ces paramètres de configuration sont définis correctement:
SET max_parallel_workers = 32; SET max_parallel_maintenance_workers = 16;
Les valeurs par défaut sont trop petites. Idéalement, ces nombres devraient être augmentés avec le nombre de cœurs de processeur. Lisez la
documentation pour plus de détails.
Création d'un index en arrière-plan
Vous pouvez créer un index en arrière-plan à l'aide du paramètre
CONCURRENTLY de la commande
CREATE INDEX :
pagila=
Cette procédure de création d'index diffère de la procédure habituelle en ce qu'elle ne nécessite pas de verrouillage de table et ne bloque donc pas les opérations d'écriture. En revanche, cela prend plus de temps et consomme plus de ressources.
Postgres fournit de nombreuses options flexibles pour créer des index et des moyens de résoudre des 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 de données prête à évoluer.