Analyse des scans dans PostgreSQL

Introduction et configuration des données

Avant de plonger, il est essentiel de comprendre les blocs élémentaires de base des plans de requête PostgreSQL. Cela a été couvert dans un article de blog séparé, et j’encourage vivement les lecteurs à le parcourir en premier.

Il existe plusieurs types de nœuds dans les plans de requête PostgreSQL,

Scans.
Se joint.
Sorte.
Agrégats etc.,

Dans cet article, nous allons approfondir Scan type de nœud, qui est probablement la pièce la plus fondamentale. Utilisons le même fake_data table qui a été utilisée dans le blog du plan de requête. Pour faciliter la compréhension, nous allons désactiver les analyses parallèles. Nous allons activer cela un peu plus tard et comprendre également les analyses parallèles dans une section distincte.

SET max_parallel_workers_per_gather = 0;

Analyses

Il y aura d’autres types de nœuds dans les exemples ci-dessous, mais l’accent sera mis uniquement sur les nœuds d’analyse et mis en évidence dans les exemples ci-dessous.

Analyses séquentielles

Cherchons un id avec valeur 1000.

C’est le plus simple de tous ; s’il n’y a pas d’index ou un nombre de lignes inférieur, le planificateur recourt à l’analyse de toutes les lignes présentes. Vous ne devriez/ne devriez jamais rencontrer d’analyses séquentielles dans un système de production typique, car elles sont très lentes et deviennent de plus en plus lentes à mesure que les données augmentent. Il existe des exceptions lorsque la table est trop petite, qu’un index est inutile et que les analyses séquentielles sont suffisamment rapides pour que vous n’ayez pas à vous inquiéter.

Analyses d’index

Créons un simple index BTree sur le id colonne pour accélérer la requête ci-dessus.

CREATE INDEX id_idx ON fake_data USING BTREE(id)

Après la création de l’index, le planificateur utilise maintenant l’index pour effectuer ce qu’on appelle une analyse d’index.

Comme vous pouvez le voir, il est nettement plus rapide (environ 3500x) qu’un scan séquentiel. Nous devons créer des index appropriés pour accélérer les requêtes en fonction de nos modèles d’accès/requête. Voici un article qui détaille le type d’index à créer pour différents modèles d’accès.

Indexer uniquement les analyses

Les analyses d’index uniquement sont très similaires aux analyses d’index, sauf qu’elles analysent uniquement les index et ne touchent pas les données de la table. Ceci n’est possible que si la requête contient la colonne indexée dans les deux SELECT et WHERE clauses. Un léger ajustement à la requête ci-dessus le démontre,

Une chose à noter est que PostgreSQL peut parfois choisir d’effectuer un scan d’index à la place d’un scan d’index uniquement si la table n’est pas bien aspirée, c’est-à-dire que le planificateur se rendra compte qu’il pourrait y avoir une certaine incohérence entre les index et les données réelles de la table. Cela peut se produire si des suppressions/insertions importantes dans/de la table et que les index n’ont pas été mis à jour. Il est recommandé de chronométrer correctement l’aspirateur automatique et d’aspirer s’il y a une opération importante comme une charge de table. Index Only Scans peuvent être très rapides en termes d’E/S et de temps puisque les index sont pratiquement mis en cache dans shared_buffers.

Analyse du tas bitmap et analyse de l’index bitmap

Comparons sur une autre colonne name qui est le contenu du texte.

CREATE INDEX name_str_idx on fake_data USING BTREE(name)

EXPLAIN ANALYZE
SELECT
    *
FROM
    fake_data
WHERE
    fake_data.name="David Bennett"

Le planificateur a décidé d’utiliser Bitmap heap Scan même s’il y avait un index BTree sur le name colonne. La raison en est que les balayages d’index provoquent des E/S aléatoires s’il n’y a pas d’ordre dans les lignes ( name est un contenu textuel). Ceci est coûteux dans les disques durs rotatifs. Pour résoudre ce problème, le planificateur adopte une approche en deux étapes. Le Bitmap Index Scan construit une structure de données appelée bitmap à partir de l’index présent et représente l’emplacement approximatif des pages sur le disque et le transmet au nœud parent, c’est-à-dire Bitmap Heap Scan, qui l’utilise ensuite pour récupérer les pages/lignes.

Il y a une discussion dans la liste de diffusion PostgreSQL, qui va en profondeur sur son fonctionnement, mais à un niveau élevé, un Bitmap Heap Scan fonctionne toujours en tandem avec Bitmap Index Scan pour accélérer les choses. Par exemple, supposons qu’il existe un grand nombre de lignes correspondantes. Dans ce cas, le planificateur décide d’effectuer une analyse de tas bitmap + une analyse d’index bitmap par rapport à une analyse d’index traditionnelle, dans laquelle l’exécuteur itère le bitmap au lieu de l’index lui-même pour des résultats plus rapides.

Bitmap Et et Bitmap Ou

La structure de données Bitmap est non seulement avantageuse pour les correspondances simples, mais peut également être combinée s’il y a deux index.

Bitmap Et est également similaire,

Gardez à l’esprit que dans certaines situations Bitmap And ou Bitmap Or ne fonctionnera pas, et nous devrons créer des index composites. Mais dans de nombreuses situations, le planificateur peut combiner très efficacement deux index individuels.

Balayages parallèles

Les scans séquentiels sont les plus lents de tous les plans que nous avons vus jusqu’à présent car il n’y a rien à optimiser là-bas. Le planificateur parcourt les données de manière séquentielle et essaie de trouver le résultat. PostgreSQL optimise cela en ajoutant du parallélisme dans les requêtes. Simulons cela en supprimant l’index pour le id colonne et exécuter la requête. Si vous utilisez la même session qu’avant, redémarrez votre client de base de données pour max_parallel_workers_per_gather paramètre à réinitialiser par défaut.

La configuration par défaut des nœuds de calcul est 2 et par conséquent, deux travailleurs ont été utilisés pour exécuter la requête. Les travailleurs sont des processus en coulisse, ce qui permet à l’exécuteur d’exécuter les analyses en parallèle. Approfondir le fonctionnement de la parallélisation est peut-être un sujet d’un autre blog. Néanmoins, la recommandation générale est de garder les travailleurs égaux au nombre de cœurs présents dans le processeur pour les résultats les plus efficaces.

Conclusion

J’espère que vous avez maintenant une bonne idée des analyses et pourquoi PostgreSQL utilise des types d’analyses spécifiques à différents endroits. La compréhension de ces types d’analyse aidera les utilisateurs à répondre à des questions telles que :

Pourquoi PostgreSQL n’utilise-t-il pas mon index dans un WHERE Comparaison?
Pourquoi PostgreSQL n’est-il pas capable de combiner deux index à l’aide de Bitmap Scan ?
Comment optimiser nos requêtes pour Index Only Scan ?

Une chose à comprendre est qu’il existe certains types de requêtes, telles que Count, Avg et d’autres requêtes agrégées, qui aboutissent toujours à une analyse séquentielle car elles doivent de toute façon analyser l’intégralité de la table pour le résultat. Restez à l’écoute pour plus d’articles sur les types de nœuds PostgreSQL.