Pourquoi ma requête SELECT DISTINCT est-elle lente sur une table Postgres avec une clé primaire composite et comment puis-je améliorer ses performances ?

Optimisation des requêtes Slow SELECT DISTINCT dans Postgres

Cet article aborde les problèmes de performances rencontrés lors de l'exécution de SELECT DISTINCT requêtes sur une grande table Postgres avec une clé primaire composite. Un scénario spécifique impliquant une table comportant près de deux millions de lignes et une clé primaire composite (product_id, trade_id) est examiné. Alors qu'une requête SELECT DISTINCT product_id devrait idéalement être rapide en raison de l'index de clé primaire, des performances étonnamment lentes ont été observées.

Analyse des causes profondes :

Le choix du planificateur de requêtes d'une analyse séquentielle, plutôt que d'utiliser l'index, a été identifié comme le goulot d'étranglement. Cela est attribué à la distribution des données de la table : seuls 40 ID de produit uniques existent, ce qui entraîne un degré élevé de répétition des valeurs d'index. Cela entraîne de nombreuses sondes d'index et un accès séquentiel inefficace.

Solution efficace : CTE récursif

Pour contourner cette limitation et exploiter efficacement l'indexation, une expression de table commune récursive (CTE) est proposée comme alternative supérieure à SELECT DISTINCT :

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses required
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;</code>

Ce CTE récursif imite efficacement une analyse de saut d'index. Il récupère de manière itérative des valeurs product_id distinctes dans un ordre trié, évitant ainsi la pénalité de performances associée à l'analyse séquentielle inefficace. L'utilisation d'un index sur la colonne product_id est cruciale pour des performances optimales avec cette approche.

Remarque importante : Bien que la fonctionnalité d'analyse des sauts d'index de Postgres soit en cours de développement, cette solution de contournement basée sur CTE offre une solution robuste et efficace pour le scénario décrit, améliorant considérablement les performances des requêtes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!