From: Yura Gal <yuragal@gmail.com>
To: pgsql-sql@postgresql.org
Subject: GiST/GIN index for field of type VARCHAR[]
Date: Thu, 3 Apr 2008 19:38:49 +0400
Message-ID: <3b6c69d80804030838k5c2bc14axd168bae52118103e@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: yuragal@gmail.com
Subject: Re: GiST/GIN index for field of type VARCHAR[]
In-Reply-To: <3b6c69d80804030838k5c2bc14axd168bae52118103e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox