I have following table: CREATE TABLE t1 ( "name" VARCHAR(500) NOT NULL, "lid" INTEGER NOT NULL, "accs" VARCHAR(20)[] NOT NULL CONSTRAINT "t1_lid_key" UNIQUE("lid") );
I interested in the possibility to speed-up search for rows like this: SELECT lid FROM t1 WHERE accs && ARRAY['item1','item2'...]::VARCHAR[]; For sure, I can use the typical way of data normalization to decrease query time: CREATE TABLE t2( "lid" INTEGER NOT NULL, "acc" VARCHAR(20) NOT NULL ); with: t2.lid = t1.lid & t1.accs @> ARRAY[t2.acc] and create hash index on acc. Then I could SELECT lid FROM t2 WHERE acc IN(item1,item2); But it's more interesting to implement GiST/GIN indexes for this purpose. And what type of index is the most suitable if VARCHAR[] arrays are 1-dimensional and contain from 1 to 20000 elements? Thanks in advance. -- Best regards. Yuri. mailto: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql