arrays - functions - ¿Pueden las columnas del conjunto de índices de PostgreSQL?

recorrer array postgresql

Question

No puedo encontrar una respuesta definitiva a esta pregunta en la documentación. Si una columna es un tipo de matriz, ¿se indexarán individualmente todos los valores ingresados?

Creé una tabla simple con una columna int[] y puse un índice único sobre ella. Me di cuenta de que no podía agregar la misma matriz de entradas, lo que me lleva a pensar que el índice es un compuesto de los elementos de la matriz, no un índice de cada elemento.

INSERT INTO "Test"."Test" VALUES (''{10, 15, 20}'');
INSERT INTO "Test"."Test" VALUES (''{10, 20, 30}'');
SELECT * FROM "Test"."Test" WHERE 20 = ANY ("Column1");

¿El índice está ayudando a esta consulta?

Answer 1

@Tregoreg planteó una pregunta en el comentario a su recompensa ofrecida:

No encontré las respuestas actuales funcionando. El uso del índice GIN en la columna de tipo matriz no aumenta el rendimiento de ningún operador ANY (). ¿Realmente no hay solución?

La respuesta aceptada de @ Frank te dice que uses operadores de arreglos , lo cual sigue siendo correcto para Postgres 10. El manual:

la distribución estándar de PostgreSQL incluye clases de operadores GIN para matrices unidimensionales, que admiten consultas indexadas utilizando estos operadores:

 <@
@>
=
&&

La lista completa de las clases de operadores incorporados para los índices GIN en la distribución estándar está aquí.

En Postgres, los índices están vinculados a operadores , no a tipos de datos o funciones ni a ninguna otra cosa. Es una herencia del diseño original de Berkeley de Postgres y muy difícil de cambiar ahora. Y generalmente está funcionando bien. Aquí hay un hilo en pgsql-bugs con Tom Lane comentando sobre esto.

La expresión indexada debe estar a la izquierda del operador. Para la mayoría de los operadores ( incluidos todos los anteriores ), el planificador de consultas puede lograrlo volteando los operandos si coloca la expresión indexada a la derecha, dado que se ha definido un COMMUTATOR . La construcción ANY se puede usar en combinación con varios operadores y no es un operador en sí mismo. Cuando se usa como constant = ANY (array_expression) solo los índices que soportan el operador = en los elementos de la matriz calificarían y necesitaríamos un conmutador para = ANY() . Los índices GIN están fuera.

Actualmente, Postgres no es lo suficientemente inteligente como para derivar una expresión indexable de GIN. Para los principiantes, constant = ANY (array_expression) no es completamente equivalente a array_expression @> ARRAY[constant] . Los operadores de matriz devuelven un error si están involucrados elementos NULL, mientras que el constructo ANY puede tratar con NULL en cualquier lado. Y hay diferentes resultados para los desajustes de tipos de datos.

Respuestas relacionadas:

• Compruebe si el valor existe en la matriz de Postgres

• Índice para encontrar un elemento en una matriz JSON

• SQLAlchemy: cómo filtrar en los tipos de columna PgArray?

• ¿Puede IS DISTINCT FROM combinarse con ANY o ALL de alguna manera?

Aparte

Al trabajar con matrices de integer ( int4 , no int2 o int8 ) sin valores NULL (como su ejemplo lo implica), considere el módulo adicional intarray , que proporciona operadores especializados y más rápidos y soporte de índices. Ver:

• ¿Cómo crear un índice para los elementos de una matriz en PostgreSQL?
• Compara arreglos para igualdad, ignorando el orden de los elementos

En cuanto a la restricción UNIQUE en su pregunta que no fue respondida: Esto se implementa con un índice btree en todo el valor de la matriz (como sospechaba) y no ayuda con la búsqueda de elementos en absoluto. Detalles:

• ¿Cómo aplica PostgreSQL la restricción ÚNICA / qué tipo de índice usa?

Answer 2

Ahora es posible indexar los elementos de la matriz individual. Por ejemplo:

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)

Esto funciona al menos en Postgres 9.2.1. Tenga en cuenta que necesita crear un índice separado para cada índice de matriz, en mi ejemplo, solo indexé el primer elemento.

Answer 3

Sí, puede indexar una matriz, pero debe usar los operadores de la matriz y el tipo de índice 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];

Resultado:

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