Re: 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/