Обсуждение: 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