DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=Bble3by0NQHGCJGAVfd4wPzfGEOuRBVSoNBfPKEeCUryLhtgaac7bC7NZu7iMC4I5Y3tC1bNm+5kxj1Vs5hhWe6xjGDsZyb4s3BL0eoIRJ/QfeNY209oIayHKocSQa1vMI1y2PpZyNRy92nuUr9Xdqz5kcTSpeSCf+MJErr0oWo=
Message-ID: <3b6c69d80804030838k5c2bc14axd168bae52118103e@mail.gmail.com>
Date: Thu, 3 Apr 2008 19:38:49 +0400
From: "Yura Gal" <yuragal@gmail.com>
To: pgsql-sql@postgresql.org
Subject: GiST/GIN index for field of type VARCHAR[]
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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: yuragal@gmail.com