PostgreSQL peut-il indexer les colonnes d'un tableau ?

Question

Je ne trouve pas de réponse précise à cette question dans la documentation. Si une colonne est de type tableau, toutes les valeurs saisies seront-elles indexées individuellement ?

J'ai créé un tableau simple avec un int[] et mettre un index unique sur elle. J'ai remarqué que je ne pouvais pas ajouter le même tableau d'ints, ce qui m'amène à penser que l'index est un composite des éléments du tableau, et non un index de chaque élément.

INSERT INTO "Test"."Test" VALUES ('{10, 15, 20}');
INSERT INTO "Test"."Test" VALUES ('{10, 20, 30}');

SELECT * FROM "Test"."Test" WHERE 20 = ANY ("Column1");

L'index aide-t-il cette requête ?

Answer 1

Oui, vous pouvez indexer un tableau, mais vous devez utiliser la fonction opérateurs de tableau y el Type d'indice GIN .

Ejemplo:

    CREATE TABLE "Test"("Column1" int[]);
    INSERT INTO "Test" VALUES ('{10, 15, 20}');
    INSERT INTO "Test" VALUES ('{10, 20, 30}');

    CREATE INDEX idx_test on "Test" USING GIN ("Column1");

    -- To enforce index usage because we have only 2 records for this test... 
    SET enable_seqscan TO off;

    EXPLAIN ANALYZE
    SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20];

Résultat :

Bitmap Heap Scan on "Test"  (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1)
  Recheck Cond: ("Column1" @> '{20}'::integer[])
  ->  Bitmap Index Scan on idx_test  (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1)
        Index Cond: ("Column1" @> '{20}'::integer[])
Total runtime: 0.062 ms

Note

il apparaît que dans de nombreux cas, le gin__int_ops L'option est requise

create index <index_name> on <table_name> using GIN (<column> gin__int_ops)

Je n'ai pas encore vu de cas où cela fonctionnerait avec l'opérateur && et @> sans les options gin__int_ops

Answer 2

@Tregoreg a soulevé un question dans le commentaire de son offre de prime :

Je n'ai pas trouvé les réponses actuelles efficaces. L'utilisation d'un index GIN sur une sur une colonne de type tableau n'augmente pas les performances de l'opérateur ANY() n'augmente pas les performances de l'opérateur ANY(). N'y a-t-il vraiment pas de solution ?

@Frank's accepted réponse vous dit d'utiliser opérateurs de tableau qui est toujours correct pour Postgres 11. Le manuel :

... la distribution standard de PostgreSQL inclut un opérateur GIN pour les tableaux, qui supporte les requêtes indexées en utilisant ces opérateurs :

<@
@>
=
&&

La liste complète des classes d'opérateurs intégrés pour les index GIN dans la distribution standard est ici.

Dans Postgres les index sont liés à des opérateurs (qui sont mises en œuvre pour certains types), et non les types de données seuls ou les fonctions ou quoi que ce soit d'autre. C'est un héritage de la conception Berkeley originale de Postgres et très difficile à changer maintenant. Et cela fonctionne généralement très bien. Voici un fil de discussion sur pgsql-bugs avec Tom Lane qui commente ce sujet.

_{Quelques PostGis <em>fonctions </em>(comme <a href="https://postgis.net/docs/ST_DWithin.html" rel="noreferrer"><code>ST_DWithin()</code></a> ) semblent violer ce principe, mais ce n'est pas le cas. Ces fonctions sont réécrites en interne pour utiliser les fonctions respectives de <em>opérateurs </em>.}

L'expression indexée doit être au gauche de l'opérateur. Pour la plupart des opérateurs ( y compris tout ce qui précède ) le planificateur de requêtes peut y parvenir en inversant les opérandes si vous placez l'expression indexée à droite - étant donné qu'un COMMUTATOR a été défini. Le site ANY construire peut être utilisé en combinaison avec divers opérateurs et n'est pas un opérateur en soi. Lorsqu'il est utilisé comme constant = ANY (array_expression) seuls les index supportant le = opérateur sur éléments du tableau serait qualifié et nous aurions besoin d'un commutateur pour = ANY() . Les indices GIN sont sortis.

Postgres n'est actuellement pas assez intelligent pour en dériver une expression indexable par GIN. Pour commencer, constant = ANY (array_expression) est pas complètement équivalentes a array_expression @> ARRAY[constant] . Les opérateurs de tableau renvoient une erreur si un NULL est présent. éléments sont impliqués, tandis que les ANY construct peut traiter avec NULL de chaque côté. Et il y a des résultats différents pour les incompatibilités de type de données.

Réponses connexes :

• Vérifier si la valeur existe dans le tableau Postgres

• Index pour trouver un élément dans un tableau JSON

• SQLAlchemy : comment filtrer sur les types de colonnes PgArray ?

• Peut-on combiner IS DISTINCT FROM avec ANY ou ALL d'une manière ou d'une autre ?

En dehors de

Tout en travaillant avec integer tableaux ( int4 pas int2 o int8 ) sans NULL (comme votre exemple l'implique), considérez le module supplémentaire intarray qui fournit des opérateurs spécialisés et plus rapides ainsi qu'un support d'index. Voir :

• Comment créer un index pour les éléments d'un tableau dans PostgreSQL ?
• Comparer des tableaux pour l'égalité, en ignorant l'ordre des éléments.

Quant à la UNIQUE contrainte dans votre question qui est restée sans réponse : C'est implémenté avec un index btree sur l'objet gamme complète (comme vous le soupçonniez) et ne contribue pas à la recherche de éléments du tout. Détails :

• Comment PostgreSQL applique-t-il la contrainte UNIQUE / quel type d'index utilise-t-il ?

Answer 3

Il est maintenant possible d'indexer les éléments individuels du tableau. Par exemple :

CREATE TABLE test (foo int[]);
INSERT INTO test VALUES ('{1,2,3}');
INSERT INTO test VALUES ('{4,5,6}');
CREATE INDEX test_index on test ((foo[1]));
SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * from test WHERE foo[1]=1;
                                                QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using test_index on test  (cost=0.00..8.27 rows=1 width=32) (actual   time=0.070..0.071 rows=1 loops=1)
   Index Cond: (foo[1] = 1)
 Total runtime: 0.112 ms
(3 rows)

Cela fonctionne au moins sur Postgres 9.2.1. Notez que vous devez construire un index séparé pour chaque index de tableau, dans mon exemple j'ai seulement indexé le premier élément.