Optimisation des performances des requêtes dans PostgreSQL

Optimisation des performances de la base de données - les développeurs aiment ou détestent généralement cela J'apprécie cela et souhaite partager certaines des méthodes que j'ai utilisées récemment pour régler les requêtes mal exécutées dans PostgreSQL. Mes méthodes ne sont pas exhaustives, mais plutôt un manuel pour ceux qui se contentent de régler.

Recherche de requêtes lentes

La première façon évidente de commencer le réglage est de trouver des opérateurs spécifiques qui fonctionnent mal.

pg_stats_statements

Le module pg_stats_statements est un excellent point de départ. Il ne fait que suivre les statistiques d'exécution des instructions SQL et peut être un moyen facile de trouver des requêtes inefficaces.

Une fois que vous avez installé ce module, une vue système appelée pg_stat_statements sera disponible avec toutes ses propriétés. Une fois qu'il a la possibilité de collecter suffisamment de données, recherchez les requêtes qui ont une valeur total_time relativement élevée . Concentrez-vous d'abord sur ces opérateurs.

SELECT * FROM pg_stat_statements ORDER BY total_time DESC; 

auto_explain

Le module auto_explain est également utile pour trouver des requêtes lentes, mais il présente 2 avantages évidents: il enregistre le plan d'exécution réel et prend en charge l'enregistrement des instructions imbriquées à l'aide de l'option log_nested_statements . Les instructions imbriquées sont des instructions qui sont exécutées à l'intérieur d'une fonction. Si votre application utilise de nombreuses fonctionnalités, auto_explain est inestimable pour obtenir des plans d'exécution détaillés.

L'option log_min_duration contrôle les plans d'exécution des requêtes qui sont enregistrés en fonction de leur durée d'exécution. Par exemple, si vous définissez la valeur sur 1000, tous les enregistrements qui durent plus d'une seconde seront enregistrés.

Réglage de l'index

Une autre stratégie de réglage importante consiste à s'assurer que les index sont utilisés correctement. Comme condition préalable, nous devons inclure le collecteur de statistiques.

Postgres Statistics Collector est un sous-système de première classe qui collecte toutes sortes de statistiques de performances utiles.

En activant ce collecteur, vous obtenez des tonnes de vues pg_stat _... qui contiennent toutes les propriétés. En particulier, j'ai trouvé cela particulièrement utile pour trouver des index manquants et inutilisés.

Index manquants

Les index manquants peuvent être l'une des solutions les plus simples pour améliorer les performances des requêtes. Cependant, ils ne sont pas une solution miracle et doivent être utilisés correctement (plus d'informations à ce sujet plus tard). Si le collecteur de statistiques est activé, vous pouvez exécuter la requête suivante ( source ).

 SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC; 

La requête trouve des tables qui ont plus d'analyses séquentielles (analyses d'index) que d'analyses d'index - une indication claire que l'index aidera. Cela ne vous dira pas sur quelles colonnes créer l'index, cela prendra donc un peu plus de travail. Cependant, savoir quelles tables en ont besoin est une bonne première étape.

Index inutilisés

Indexer toutes les entités, non? Saviez-vous que les index non utilisés peuvent nuire aux performances d'écriture? La raison en est que lors de la création de l'index Postgres, il est chargé de mettre à jour cet index après les opérations d'écriture (INSERT / UPDATE / DELETE). Ainsi, l'ajout d'un index est un acte d'équilibrage, car il peut accélérer la lecture des données (s'il a été créé correctement), mais il ralentira les opérations d'écriture. Pour rechercher des index inutilisés, vous pouvez exécuter la requête suivante.

 SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false; 

Remarque sur les statistiques de l'environnement de développement

Se fier aux statistiques d'une base de données de développement local peut être problématique. Idéalement, vous pouvez obtenir les statistiques ci-dessus à partir de votre machine de travail ou les générer à partir d'une sauvegarde de travail restaurée. Pourquoi? Les facteurs environnementaux peuvent modifier le comportement de l'optimiseur de requête Postgres. Deux exemples:

• lorsque la machine a moins de mémoire, PostgreSQL peut ne pas être en mesure d'effectuer une jointure par hachage, sinon elle le fera et le fera plus rapidement.
• s'il n'y a pas autant de lignes dans la table (comme dans la base de données de développement), PostgresSQL peut préférer effectuer une analyse séquentielle de la table plutôt que d'utiliser un index disponible. Lorsque les tailles de table sont petites, Seq Scan peut être plus rapide. (Remarque: vous pouvez exécuter

   SET enable_seqscan = OFF 

  dans une session afin que l'optimiseur choisisse d'utiliser des index, même si les analyses séquentielles peuvent être plus rapides. Ceci est utile lorsque vous travaillez avec des bases de données de développement qui ne contiennent pas beaucoup de données)

Comprendre les plans d'exécution

Maintenant que vous avez trouvé quelques requêtes lentes, il est temps de commencer à vous amuser.

EXPLIQUER

La commande EXPLAIN est certainement requise lors de la configuration des requêtes. Il vous dit ce qui se passe vraiment. Pour l'utiliser, il suffit d'ajouter EXPLAIN à la requête et de l'exécuter. PostgreSQL vous montrera le plan d'exécution qu'il a utilisé.

Lorsque vous utilisez EXPLAIN pour le réglage, je recommande de toujours utiliser l'option ANALYZE ( EXPLAIN ANALYZE ), car elle vous donne des résultats plus précis. L'option ANALYSER exécute en fait l'instruction (plutôt que de simplement l'évaluer), puis l'explique.

Prenons un plongeon et commençons à comprendre la sortie d' EXPLAIN . Voici un exemple:

Noeuds

La première chose à comprendre est que chaque bloc en retrait avec le précédent «->» (avec la ligne supérieure) est appelé un nœud. Un nœud est une unité de travail logique (une «étape», si vous le souhaitez) avec un coût et un délai d'exécution associés. Le coût et le temps présentés sur chaque nœud sont cumulatifs et rassemblent tous les nœuds enfants. Cela signifie que la ligne la plus haute (nœud) indique le coût total et le temps réel pour l'opérateur entier. Ceci est important car vous pouvez facilement explorer en avant pour déterminer quels nœuds sont le goulot d'étranglement.

Coût

 cost=146.63..148.65 

Le premier nombre est le coût initial (le coût d'obtention du premier enregistrement), et le deuxième nombre est le coût de traitement de l'ensemble du nœud (coût total du début à la fin).

En fait, c'est le coût que les estimations de PostgreSQL devront être satisfaites pour exécuter l'instruction. Ce nombre ne signifie pas combien de temps il faudra pour répondre à la demande, bien qu'il y ait généralement une relation directe nécessaire pour terminer. Le coût est une combinaison de 5 éléments de travail utilisés pour évaluer le travail requis: échantillonnage séquentiel, échantillonnage incohérent (aléatoire), traitement en ligne, opérateur de traitement (fonction) et enregistrement de l'indice de traitement. Le coût est l'entrée / sortie et la charge du processeur, et il est important de savoir que le coût relativement élevé signifie que PostgresSQL pense qu'il devra faire plus de travail. L'optimiseur décide du plan d'exécution à utiliser en fonction du coût. L'optimiseur préfère des coûts inférieurs.

Heure réelle

 actual time=55.009..55.012 

En millisecondes, le premier nombre est l'heure de début (temps pour récupérer le premier enregistrement) et le deuxième nombre est le temps requis pour traiter le nœud entier (temps total du début à la fin). Facile à comprendre, non?

Dans l'exemple ci-dessus, il a fallu 55,009 ms pour obtenir le premier enregistrement et 55,012 ms pour terminer le nœud entier.

En savoir plus sur les plans d'exécution.

Il existe de très bons articles pour comprendre les résultats EXPLAIN. Au lieu d'essayer de les raconter ici, je recommande de prendre le temps de vraiment les comprendre en allant vers ces 2 merveilleuses ressources:

• http://www.depesz.com/2013/04/16/explaining-the-unexplainable/
• https://wiki.postgresql.org/images/4/45/Explaining_EXPLAIN.pdf

Demander un réglage

Maintenant que vous savez quels opérateurs fonctionnent mal et que vous pouvez voir vos plans d'exécution, il est temps de commencer à régler votre requête pour améliorer les performances. Ici, vous modifiez vos requêtes et / ou ajoutez des index pour essayer d'obtenir un meilleur plan d'exécution. Commencez par les goulots d'étranglement et voyez s'il y a des changements que vous pouvez apporter pour réduire les coûts et / ou les délais.

Cache de données et note de coût

Lors des modifications et de l'évaluation des plans d'implémentation, afin de voir s'il y aura des améliorations, il est important de savoir que les implémentations futures peuvent dépendre de la mise en cache des données qui donne une idée des meilleurs résultats. Si vous exécutez la demande une fois, apportez une correction et exécutez-la une deuxième fois, il est probable qu'elle s'exécute beaucoup plus rapidement, même si le plan d'exécution n'est pas plus favorable. En effet, PostgreSQL pourrait mettre en cache les données utilisées au premier démarrage et peut les utiliser au deuxième démarrage. Par conséquent, vous devez effectuer les requêtes au moins 3 fois et faire la moyenne des résultats pour comparer les coûts.

Les choses que j'ai apprises peuvent aider à améliorer les plans d'exécution:

• Indices
  
  • Exclure l'analyse séquentielle (Seq Scan) en ajoutant des index (si la taille de la table n'est pas petite)
  • Lorsque vous utilisez un index multi-colonnes, assurez-vous de faire attention à l'ordre dans lequel vous définissez les colonnes incluses - Plus d'informations
  • Essayez des index très sélectifs pour les données fréquemment utilisées. Cela rendra leur utilisation plus efficace.
• Condition OERE
  
  
  • Évitez comme
  • Évitez les appels de fonction dans la clause WHERE
  • Évitez les grosses conditions dans ()
• JOINS
  
  
  • Lorsque vous joignez des tables, essayez d'utiliser une expression d'égalité simple dans la clause ON (c'est-à-dire a.id = b.person_id). Cela vous permet d'utiliser des méthodes de jointure plus efficaces (c'est-à-dire la jointure par hachage, pas la jointure par boucle imbriquée)
  • Convertissez les sous-requêtes en instructions JOIN lorsque cela est possible, car cela permet généralement à l'optimiseur de comprendre l'objectif et éventuellement de choisir le meilleur plan.
  • Utilisez les COMPOSÉS correctement: utilisez-vous GROUP BY ou DISTINCT juste parce que vous obtenez des résultats en double? Cela indique généralement une mauvaise utilisation des JOIN et peut entraîner des coûts plus élevés.
  • Si le plan d'exécution utilise Hash Join, il peut être très lent si les estimations de taille de table sont incorrectes. Par conséquent, assurez-vous que les statistiques de votre table sont exactes en examinant la stratégie d'aspiration.
  • Évitez autant que possible les sous-requêtes corrélées ; ils peuvent augmenter considérablement le coût d'une demande
  • Utilisez EXISTS lors de la vérification de l'existence de chaînes en fonction d'un critère, car il est similaire à un court-circuit (arrête le traitement lorsqu'il trouve au moins une correspondance)
• Recommandations générales
  
  
  • Faites plus avec moins; Processeur plus rapide que les entrées / sorties (E / S)
  • Utilisez les expressions de table communes et les tables temporaires lorsque vous devez effectuer des requêtes chaînées.
  • Évitez les instructions LOOP et préférez les opérations SET
  • Évitez COUNT (*) car PostgresSQL analyse les tables pour cela ( uniquement pour les versions <= 9.1 )
  • Dans la mesure du possible, évitez ORDER BY, DISTINCT, GROUP BY, UNION, car cela entraîne des coûts initiaux élevés.
  • Recherchez la grande différence entre les lignes estimées et réelles dans l'expression EXPLAIN . Si le compteur est très différent, les statistiques de la table peuvent être obsolètes et PostgreSQL estime le coût en utilisant des statistiques inexactes. Par exemple:

     Limit (cost=282.37..302.01 rows=93 width=22) (actual time=34.35..49.59 rows=2203 loops=1) 

    Le nombre estimé de lignes était de 93, et le réel - 2203. Par conséquent, il s'agit très probablement d'une mauvaise décision du plan. Vous devez revoir votre stratégie d'aspiration et vous assurer que l'ANALYSE est exécutée assez souvent.