¿Puede PostgreSQL indexar columnas de matriz?
No puedo encontrar una respuesta definitiva a esta pregunta en la documentación. Si una columna es de tipo matriz, ¿se indexarán individualmente todos los valores ingresados?
Creé una tabla simple con una int[]columna y le puse un índice único. Noté que no podía agregar la misma matriz de entradas, lo que me lleva a creer que el índice es una combinación 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 ayuda a esta consulta?
Sí, puedes indexar una matriz, pero debes usar los operadores de 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" gin__int_ops);
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
Nota
parece que en muchos casos se requiere la opción gin__int_ops
create index <index_name> on <table_name> using GIN (<column> gin__int_ops)
Todavía no he visto un caso en el que funcione con el operador && y @> sin las opciones gin__int_ops
@Tregoreg planteó una pregunta en el comentario sobre la recompensa ofrecida:
No encontré que las respuestas actuales funcionaran. El uso del índice GIN en una columna de tipo matriz no aumenta el rendimiento del operador ANY(). ¿Realmente no hay solución?
La respuesta aceptada de @Frank le indica que use operadores de matriz , lo cual sigue siendo correcto para Postgres 11. El manual:
... la distribución estándar de PostgreSQL incluye una clase de operador GIN para matrices, que admite consultas indexadas utilizando estos operadores:
<@ @> = &&
La lista completa de clases de operadores integradas para índices GIN en la distribución estándar está aquí.
En Postgres, los índices están vinculados a operadores (que se implementan para ciertos tipos), no a tipos de datos solos, funciones o cualquier otra cosa. Esa es una herencia del diseño original de Postgres en Berkeley y es muy difícil de cambiar ahora. Y en general funciona bien. Aquí hay un hilo sobre pgsql-bugs con Tom Lane comentando sobre esto.
Algunas funciones de PostGis (como ST_DWithin()) parecen violar este principio, pero no es así. Esas funciones se reescriben internamente para utilizar los operadores respectivos .
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 lograr esto invirtiendo los operandos si coloca la expresión indexada a la derecha, dado que COMMUTATORse ha definido a. La ANYconstrucción se puede utilizar en combinación con varios operadores y no es un operador en sí. Cuando se usan como constant = ANY (array_expression)índices que admiten el =operador en elementos de matriz , solo 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 con GIN. Para empezar, no constant = ANY (array_expression)es completamente equivalente a array_expression @> ARRAY[constant]. Los operadores de matriz devuelven un error si hay elementos NULL involucrados, mientras que la ANYconstrucción puede tratar con NULL en cualquier lado. Y hay diferentes resultados para las discrepancias en los 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 por tipos de columnas PgArray?
¿Se puede combinar ES DISTINTO DE con CUALQUIERA o TODOS de alguna manera?
Apartes
Mientras trabaja con integermatrices ( int4, no int2o int8) sin NULLvalores (como lo implica su ejemplo), considere el módulo adicional intarray, que proporciona operadores especializados y más rápidos y soporte de índice. Ver:
- ¿Cómo crear un índice para elementos de un array en PostgreSQL?
- Comparar matrices para determinar la igualdad, ignorando el orden de los elementos
En cuanto a la UNIQUErestricción en su pregunta que quedó sin respuesta: se implementa con un índice btree en todo el valor de la matriz (como sospechaba) y no ayuda en absoluto con la búsqueda de elementos . Detalles:
- ¿Cómo aplica PostgreSQL la restricción ÚNICA/qué tipo de índice utiliza?
Ahora es posible indexar los elementos individuales de la matriz. 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.
