Обсуждение: Creating GiST Indices?

I've been playing around with creating (or rather, attempting to create)
GiST indices. I've looked high and low for information on how this sort of
thing is done, and I'm at the end of my rope.

Here's basically what I've been using as a test:

testdb=# create table test (
testdb-#    id serial primary key,
testdb-#    sometext text not null
testdb-# );
...
testdb=# insert into test (sometext) values ('blah');
testdb=# insert into test (sometext) values ('whatever');
testdb=# insert into test (sometext) values ('filling up space');
...
testdb=# create index my_index on test using gist (sometext);
ERROR:  data type text has no default operator class for access method
"gist" You must specify an class for the index or define a default operator
class for the data type


So I've tried stuff like this, based on various things I've read about GiST
in PgSQL:

testdb=# create index my_index on test using gist (sometext gist_text_ops);
ERROR:  DefineIndex: operator class "gist_text_ops" not support by access
method "gist"

This basically went on for hours. I've tried text_ops based on what I can
see in the pg_class table and a bunch of other stuff, but I can't create
GiST indexes. (Or rather, I don't know how to.)

What should I be doing instead? I've looked at OpenFTS, contrib/tsearch, but
I'm no closer to getting it working.

Any pointers?

Thx.

J

J Smith <dark_panda@hushmail.com> writes:
> This basically went on for hours. I've tried text_ops based on what I can
> see in the pg_class table and a bunch of other stuff, but I can't create
> GiST indexes. (Or rather, I don't know how to.)

The reason you're not getting anywhere is that there aren't any GiST
operator classes in the standard distribution; thus, no way to apply
the GiST code to any datatype.

The various contrib modules that make use of GiST create custom operator
classes --- and, in many cases, custom datatypes as well for the custom
opclasses to work on.

You're right that this is all very poorly documented.  Making new index
opclasses will probably always be a task for wizards, but it'd be nice
if there were more info in the docs about it :-(.  You can try reading
the presentation in the 7.3 devel docs:
    http://developer.postgresql.org/docs/postgres/xindex.html
but note that that does not apply to prior releases; if you actually
want to try this in a current release, next read
    http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/xindex.html
to see the ugly manual procedure that CREATE OPERATOR CLASS replaces.

            regards, tom lane

Thanks for the help. I was near insanity trying to figure out the meaning of
the error codes and fine some docs. The docs are a bit misleading, I guess,
as they sort of say "you can use btrees, rtrees and GiST", but don't
mention that you need to actually do something special to get GiST working.

Anyway, I'll take a look at the docs you mentioned and play around with the
code in contrib/tsearch. It's something close to what I'm looking for...

Thx.

J


Tom Lane wrote:

> J Smith <dark_panda@hushmail.com> writes:
>> This basically went on for hours. I've tried text_ops based on what I can
>> see in the pg_class table and a bunch of other stuff, but I can't create
>> GiST indexes. (Or rather, I don't know how to.)
>
> The reason you're not getting anywhere is that there aren't any GiST
> operator classes in the standard distribution; thus, no way to apply
> the GiST code to any datatype.
>
> The various contrib modules that make use of GiST create custom operator
> classes --- and, in many cases, custom datatypes as well for the custom
> opclasses to work on.
>
> You're right that this is all very poorly documented.  Making new index
> opclasses will probably always be a task for wizards, but it'd be nice
> if there were more info in the docs about it :-(.  You can try reading
> the presentation in the 7.3 devel docs:
> http://developer.postgresql.org/docs/postgres/xindex.html
> but note that that does not apply to prior releases; if you actually
> want to try this in a current release, next read
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/xindex.html
> to see the ugly manual procedure that CREATE OPERATOR CLASS replaces.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html