Thread: Using GIN indexes on 8.2
Two questions about GIN on 8.2. There's not much documentation about
GIN, but this should be possible:
create table foo (values text[]);
create index foo_values_index on foo using gin (text);
However, this then fails saying the operator "@" does not exist:
select * from foo where values @ '{hello, world}'
Do I need to reference a specific opclass when creating the index?
From the documentation I got the impression that GIN bundled
operators for most built-in types.
Secondly, are GIN indexes immutable and (unlike Tsearch2) non-lossy
and therefore useful with functional indexes? I would like to do this:
create table bar (value text);
create index bar_value_index on bar using gin (analyze(value));
where analyze() is a function of my own that tokenizes, stems and
filters the text into a text[] array.
Alexander.
Alexander Staubo wrote:
> Two questions about GIN on 8.2. There's not much documentation about
> GIN, but this should be possible:
>
> create table foo (values text[]);
> create index foo_values_index on foo using gin (text);
>
> However, this then fails saying the operator "@" does not exist:
>
> select * from foo where values @ '{hello, world}'
Use @>, <@ operations instead of @ and ~
Look for discussions in -hackers for reasons of changing names
> Do I need to reference a specific opclass when creating the index? From
> the documentation I got the impression that GIN bundled operators for
> most built-in types.
if there is a default opclass for your datatype - you may do not specify.
>
> Secondly, are GIN indexes immutable and (unlike Tsearch2) non-lossy and
> therefore useful with functional indexes? I would like to do this:
>
> create table bar (value text);
> create index bar_value_index on bar using gin (analyze(value));
>
> where analyze() is a function of my own that tokenizes, stems and
> filters the text into a text[] array.
Be careful -
select
pg_opclass.opcname,
pg_operator.oprname,
pg_amop.amopreqcheck
from
pg_opclass,
pg_operator,
pg_amop,
pg_am
where
pg_operator.oid = pg_amop.amopopr and
pg_opclass.oid = pg_amop.amopclaid and
pg_opclass.opcamid = pg_am.oid and
pg_am.amname='gin' and pg_opclass.opcname='_text_ops';
opcname | oprname | amopreqcheck
-----------+---------+--------------
_text_ops | && | f
_text_ops | @> | f
_text_ops | <@ | t
_text_ops | = | t
(4 rows)
So, operations <@ and = will recheck result with table's row.
Pls, why don't you use tsearch2 with GIN?
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Nov 10, 2006, at 16:16 , Teodor Sigaev wrote:
> Alexander Staubo wrote:
>> Two questions about GIN on 8.2. There's not much documentation
>> about GIN, but this should be possible:
>> create table foo (values text[]);
>> create index foo_values_index on foo using gin (text);
>> However, this then fails saying the operator "@" does not exist:
>> select * from foo where values @ '{hello, world}'
>
> Use @>, <@ operations instead of @ and ~
> Look for discussions in -hackers for reasons of changing names
Ah, many thanks. How about updating those web pages? :)
> Pls, why don't you use tsearch2 with GIN?
Perhaps -- is there any documentation on this?
Alexander.
>> Use @>, <@ operations instead of @ and ~ >> Look for discussions in -hackers for reasons of changing names > > Ah, many thanks. How about updating those web pages? :) Now they are in core: http://developer.postgresql.org/pgdocs/postgres/functions-array.html > >> Pls, why don't you use tsearch2 with GIN? > > Perhaps -- is there any documentation on this? Nothing special, just use GIN index instead of GiST. -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote: > >> Use @>, <@ operations instead of @ and ~ > >> Look for discussions in -hackers for reasons of changing names > > > > Ah, many thanks. How about updating those web pages? :) > Now they are in core: > http://developer.postgresql.org/pgdocs/postgres/functions-array.html > > > > >> Pls, why don't you use tsearch2 with GIN? > > > > Perhaps -- is there any documentation on this? > Nothing special, just use GIN index instead of GiST. > I think a section showing when to use GiST versus GIN would be very helpful (assuming there are still good reasons to use GiST). I would certainly read it. It would also be helpful to update the docs to use GIN where appropriate. Regards, Jeff Davis
On Friday 10 November 2006 14:41, Jeff Davis wrote: > On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote: > > >> Use @>, <@ operations instead of @ and ~ > > >> Look for discussions in -hackers for reasons of changing names > > > > > > Ah, many thanks. How about updating those web pages? :) > > > > Now they are in core: > > http://developer.postgresql.org/pgdocs/postgres/functions-array.html > > > > >> Pls, why don't you use tsearch2 with GIN? > > > > > > Perhaps -- is there any documentation on this? > > > > Nothing special, just use GIN index instead of GiST. > > I think a section showing when to use GiST versus GIN would be very > helpful (assuming there are still good reasons to use GiST). I would > certainly read it. > I was wondering this same thing, but it appears the trade-off is disk space/update speed (GiST) vs. query times (Gin). Magnus had a couple of nice posts in his blog you might want to check out: http://people.planetpostgresql.org/mha/ -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
On Sat, 11 Nov 2006, Robert Treat wrote: > On Friday 10 November 2006 14:41, Jeff Davis wrote: >> On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote: >>>>> Use @>, <@ operations instead of @ and ~ >>>>> Look for discussions in -hackers for reasons of changing names >>>> >>>> Ah, many thanks. How about updating those web pages? :) >>> >>> Now they are in core: >>> http://developer.postgresql.org/pgdocs/postgres/functions-array.html >>> >>>>> Pls, why don't you use tsearch2 with GIN? >>>> >>>> Perhaps -- is there any documentation on this? >>> >>> Nothing special, just use GIN index instead of GiST. >> >> I think a section showing when to use GiST versus GIN would be very >> helpful (assuming there are still good reasons to use GiST). I would >> certainly read it. >> > > I was wondering this same thing, but it appears the trade-off is disk > space/update speed (GiST) vs. query times (Gin). Magnus had a couple of nice > posts in his blog you might want to check out: > http://people.planetpostgresql.org/mha/ This is written in tsearch2 documentation http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html#indexes Also, see our slides http://www.sai.msu.su/~megera/wiki/tsearch2slides The best practice is to use GIN for archive search and GiST for online one. Table inheritabce with CE would help maintaining of good search performance and fast update. Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83