Pourquoi ma requête PostgreSQL SELECT DISTINCT avec une clé primaire composite est-elle si lente ?

PostgreSQL SELECT DISTINCT Problèmes de performances avec les clés composites

Utiliser SELECT DISTINCT sur une table PostgreSQL avec une clé primaire composite (par exemple, (product_id, trade_id)) peut être étonnamment lente. Le planificateur de requêtes choisit souvent une analyse séquentielle au lieu d'utiliser efficacement l'index.

Pourquoi est-ce lent ?

• Préférence d'analyse séquentielle : Le planificateur peut préférer une analyse complète de la table, même avec un index, ce qui entraîne des temps de requête beaucoup plus longs.
• Analyse des sauts d'index manquants : PostgreSQL ne prend pas en charge nativement les analyses des sauts d'index, une optimisation qui récupère efficacement les valeurs uniques en ignorant les doublons dans l'index.

La solution : simuler l'analyse des sauts d'index avec un CTE

Bien qu'une véritable analyse de saut d'index ne soit pas disponible, nous pouvons efficacement imiter son comportement à l'aide d'une expression de table commune (CTE) récursive :

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   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
      ORDER  BY 1
      LIMIT  1
      ) l
   )
SELECT * FROM cte;</code>

Ce CTE parcourt des valeurs product_id uniques dans un ordre trié, en tirant parti de l'index sur (product_id) pour plus d'efficacité.

Avantages de cette approche

• Amélioration de la vitesse : Cette méthode réduit considérablement le temps d'exécution des requêtes. Les tests montrent une réduction à 0,75 milliseconde sur une table de 2,25 millions de lignes.
• Utilisation de l'index : Il utilise efficacement à la fois l'index de clé primaire composite (product_id, trade_id) et l'index sur (product_id).
• Agnostique de la distribution des données : Les performances restent cohérentes quelle que soit la distribution des données dans la table.

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!