Indexation avancée impliquant des conditions OR (pgsql)

Question

Je commence à avoir une bien meilleure compréhension de l'indexation PostgreSQL, mais j'ai rencontré un problème avec la condition OR, où je ne sais pas comment optimiser mes index pour une requête plus rapide.

J'ai 6 conditions qui, lorsqu'elles sont exécutées individuellement, semblent avoir un coût faible. Voici un exemple des requêtes simplifiées, y compris les temps de calcul du plan de requête.

(REMARQUE: Je n'ai pas affiché les plans de requête réels pour ces requêtes ci-dessous dans le but de réduire la complexité, mais elles utilisent toutes des jointures imbriquées gauches et des analyse d'index comme je m'y attends avec une indexation appropriée. Si nécessaire, je peux inclure les plans de requête pour une réponse plus significative.)

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1)
 LIMIT 10;

PLAN DE REQUÊTE
-------------------------------------------------------------------------------------
Limit  (cost=0.25..46.69 rows=1 width=171) (temps réel=0.031..0.031 lignes=0 boucles=1)

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions2)
 LIMIT 10;

PLAN DE REQUÊTE
-------------------------------------------------------------------------------------
Limit  (cost=0.76..18.97 rows=1 width=171) (temps réel=14.764..14.764 lignes=0 boucles=1)

/* snip */

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions6)
 LIMIT 10;

PLAN DE REQUÊTE
-------------------------------------------------------------------------------------
Limit  (cost=0.51..24.48 rows=1 width=171) (temps réel=0.252..5.332 lignes=10 boucles=1)

Mon problème est que je veux combiner ces 6 conditions avec des opérateurs OR, faisant de chaque condition une possibilité. Ma requête combinée ressemble davantage à ceci :

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1 OR conditions2 OR conditions3 OR conditions4 OR conditions5 OR conditions 6)
 LIMIT 10;

Malheureusement, cela entraîne une AUGMENTATION MASSIVE du plan de requête, qui ne semble plus utiliser mes index (au lieu de cela, choisissant de faire une jointure de hachage au lieu d'une jointure imbriquée, et effectuant diverses analyses séquentielles sur les analyses d'index précédemment utilisées).

Limit  (cost=142.62..510755.78 rows=1 width=171) (temps réel=30.591..30.986 lignes=10 boucles=1)

Y a-t-il quelque chose de spécial que je devrais savoir sur l'indexation concernant les conditions en OR qui améliorerait ma requête finale ?

MISE À JOUR : Si j'utilise une UNION pour chaque SELECT individuel, cela semble accélérer la requête. Cependant, est-ce que cela m'empêchera d'ordonner mes résultats si je choisis de le faire à l'avenir ? Voici ce que j'ai fait pour accélérer la requête via UNION :

EXPLAIN ANALYZE
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions2)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions3)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions4)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions5)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 ON t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions6)
 LIMIT 10;

PLAN DE REQUÊTE
-------------------------------------------------------------------------------------
Limit  (cost=219.14..219.49 rows=6 width=171) (temps réel=125.579..125.653 lignes=10 boucles=1)

Answer 1

En fonction des conditions, il peut être logiquement impossible d'utiliser un index pour aider une condition complexe en utilisant des expressions OR.

Comme MySQL, PostgreSQL 8.0 et les versions antérieures le précisent dans leur documentation sur les indexes:

Notez qu'une requête ou une commande de manipulation de données ne peut utiliser qu'un index par table.

Avec PostgreSQL 8.1, cela a changé.

Cependant, si cela ne vous aide pas, vous pouvez utiliser la solution UNION que vous avez essayée (c'est une solution courante pour les utilisateurs de MySQL, qui continue d'avoir une limitation d'un index par table).

Vous devriez être capable d'ordonner les résultats d'une requête UNION, mais vous devez utiliser des parenthèses pour indiquer que l'instruction ORDER BY s'applique au résultat de l'instruction UNION, et non simplement à la dernière sous-requête de la chaîne.

(SELECT ... )
UNION
(SELECT ... )
UNION
(SELECT ... )
ORDER BY nom_colonne;

---

J'espère que cela vous aide ; je ne suis pas un expert de l'optimiseur PostgreSQL. Vous pouvez essayer de chercher dans les archives de la liste de diffusion, ou demander sur le canal IRC.

Answer 2

(Désolé - je ne sais pas comment répondre à une réponse, donc celle-ci remonte au niveau supérieur)

Pour clarifier - PG utilisait auparavant un seul index pour une seule analyse de table. Si vous avez une requête joignant trois tables et que chacune a un index utile, il était toujours assez intelligent pour utiliser les trois.

Dans votre cas particulier, ce qui se passe probablement, c'est que vous avez une certaine connexion entre vos conditions OR. PostgreSQL ne le sait pas, et finit donc par supposer qu'il correspondra à plus de lignes qu'il ne le fait réellement. Assez de lignes pour changer votre plan de requête.

Aussi, vos requêtes UNION ne sont pas tout à fait les mêmes que les individuelles puisque vous LIMITEZ chacune d'elles séparément plutôt que l'ensemble du jeu de résultats avec l'UNION.

Vous devriez pouvoir ordonner les résultats d'une requête UNION, mais vous devez utiliser des parenthèses pour spécifier que l'ORDER BY s'applique au résultat de l'UNION, et non simplement à la dernière sous-requête de la chaîne.

Ce n'est pas correct - l'ORDER BY s'applique à l'ensemble du résultat.

HTH