Postgres – Équivalence manquée sur les index d’Arrays

Expliquons la situation avec un exemple concret. Étant donné la structure de tableau suivante :

CREATE TABLE data (
    name TEXT PRIMARY KEY,
    categories TEXT[]
);

Les deux requêtes suivantes donnent exactement le même résultat.

1. SELECT COUNT(*) FROM data WHERE 'cafes' = ANY(categories);
2. SELECT COUNT(*) FROM data WHERE categories && ARRAY['cafes']::TEXT[];

Pour y parvenir, elles utilisent des opérateurs différents et, sous le capot, la différence n’est pas évidente. La première, qui utilise l’opérateur ANY, itère sur l’array des catégories et retourne vrai dès qu’une correspondance est trouvée. La seconde utilise « && », l’opérateur de chevauchement, et renvoie un résultat positif si les tableaux ont des éléments en commun. Dans notre cas, le tableau de droite n’a qu’un seul élément, la requête est donc équivalent à la première version, qui utilise l’opérateur ANY.

L’enjeu, c’est que si vous devez indexer des catégories spécifiques, l’index doit correspondre exactement aux requêtes. En suivant le même ordre, vous auriez besoin de

1. CREATE INDEX idx_categories_2_any ON data (name) WHERE 'cafes' = ANY(categories); pour la première requête.
2. CREATE INDEX idx_categories_2_intersect ON data (name) WHERE categories && ARRAY['cafes']::TEXT[]; pour la deuxième requête.

Même si les requêtes donnent des résultats identiques, il semble que le moteur reconnaisse pas l’équivalence. Si vous n’avez que l’index « ANY » et que vous utilisez la requête « && », le moteur n’utilisera pas l’index. L’inverse est également vrai : si vous n’avez que l’index « && » et que vous utilisez la requête « ANY », le moteur n’utilisera pas l’index non plus.

J’ai confirmé que c’est le cas pour les versions 15 à 17 de Postgres. Je n’ai pas essayé la version 14 ou les versions inférieures.

Si vous vous demandez si un formulation est plus performante que l’autre, il semble qu’elles soient identiques. Je l’ai testé sur des données de production chez Local Logic, sur notre table de points d’intérêts, qui a plus de 6 millions de lignes, avec 29 catégories. L’exécution de EXPLAIN (analyse, buffers) SELECT… sur les deux formes de la requête a donné exactement le même coût.