Using GIN indexes on 8.2

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