GiST/GIN index for field of type VARCHAR[]

Поиск
Список
Период
Сортировка
От Yura Gal
Тема GiST/GIN index for field of type VARCHAR[]
Дата
Msg-id 3b6c69d80804030838k5c2bc14axd168bae52118103e@mail.gmail.com
обсуждение исходный текст
Список pgsql-sql
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


В списке pgsql-sql по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: BROBLEM IN BETWEEN QUERY (plpgsql)
Следующее
От: "Marcin Krawczyk"
Дата:
Сообщение: connections between servers