Possono le colonne dell’array di indice PostgreSQL?

Non riesco a trovare una risposta definitiva a questa domanda nella documentazione. Se una colonna è di tipo array, tutti i valori inseriti saranno indicizzati individualmente?

Ho creato una tabella semplice con una colonna int[] e ho inserito un indice univoco. Ho notato che non potevo aggiungere lo stesso array di int, il che mi porta a credere che l’indice sia un composto degli elementi dell’array, non un indice di ciascun 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"); 

L’indice aiuta questa query?

Sì, è ansible indicizzare un array, ma è necessario utilizzare gli operatori dell’array e il tipo di indice GIN .

Esempio:

  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]; 

Risultato:

 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

sembra che in molti casi sia richiesta l’opzione gin__int_ops

 create index  on  using GIN ( gin__int_ops) 

Non ho ancora visto un caso in cui avrebbe funzionato con l’operatore && e @> senza le opzioni gin__int_ops

@Tregoreg ha sollevato una domanda nel commento alla sua offerta di:

Non ho trovato le risposte attuali funzionanti. L’uso dell’indice GIN sulla colonna digitata in array non aumenta le prestazioni dell’operatore ANY (). Non c’è davvero nessuna soluzione?

@ La risposta accettata di Frank ti dice di usare gli operatori di array , che è ancora corretto per Postgres 10. Il manuale:

la distribuzione standard di PostgreSQL include classi di operatori GIN per array monodesmensionali, che supportano query indicizzate che utilizzano questi operatori:

  <@ @> = && 

L’elenco completo delle classi di operatori incorporati per gli indici GIN nella distribuzione standard è qui.

Gli indici di Postgres sono legati agli operatori , non ai tipi di dati o alle funzioni o altro. È un’eredità del design originale di Postgres di Berkeley e molto difficile da cambiare ora. E generalmente funziona bene. Ecco una discussione su pgsql-bugs con Tom Lane che commenta questo.

L’espressione indicizzata deve essere a sinistra dell’operatore. Per la maggior parte degli operatori ( incluso tutto quanto sopra ) il pianificatore di query può ottenere ciò ribaltando gli operandi se si posiziona l’espressione indicizzata a destra, dato che è stato definito un COMMUTATOR . Il costrutto ANY può essere utilizzato in combinazione con vari operatori e non è un operatore stesso. Se usato come constant = ANY (array_expression) solo gli indici che supportano l’operatore = sugli elementi dell’array sarebbero idonei e avremmo bisogno di un commutatore per = ANY() . Gli indici GIN sono fuori.

Postgres non è al momento abbastanza intelligente da derivarne un’espressione indicizzabile tramite GIN. Per i principianti, constant = ANY (array_expression) non è completamente equivalente a array_expression @> ARRAY[constant] . Gli operatori di array restituiscono un errore se sono coinvolti elementi NULL, mentre il costrutto ANY può gestire NULL su entrambi i lati. E ci sono risultati diversi per le mancate corrispondenze del tipo di dati.

Risposte correlate:

  • Controlla se il valore esiste nell’array Postgres

  • Indice per trovare un elemento in un array JSON

  • SQLAlchemy: come filtrare i tipi di colonna PgArray?

  • Può IS DISTINCT FROM essere combinato con QUALSIASI o TUTTO in qualche modo?

asides

Mentre si lavora con array integer ( int4 , non int2 o int8 ) senza valori NULL (come il tuo esempio implica) si consideri l’ intarray modulo intarray , che fornisce operatori specializzati e più veloci e supporto indice. Vedere:

  • Come creare un indice per elementi di un array in PostgreSQL?
  • Confronta gli array per l’uguaglianza, ignorando l’ordine degli elementi

Per quanto riguarda il vincolo UNIQUE nella tua domanda che non ha risposto: è implementato con un indice btree sull’intero valore dell’array (come si sospetta) e non aiuta affatto la ricerca di elementi . Dettagli:

  • In che modo PostgreSQL applica il vincolo UNIQUE / che tipo di indice usa?

Ora è ansible indicizzare i singoli elementi dell’array. Per esempio:

 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) 

Funziona almeno su Postgres 9.2.1. Si noti che è necessario creare un indice separato per ogni indice di array, nel mio esempio ho solo indicizzato il primo elemento.