Re: Question about indexes and operator classes

ERR ORR <rd0002(at)gmail(dot)com> writes:
> 1) What is the difference between *varchar_pattern_ops* and *
> varchar_text_ops*?

None whatsoever --- they're both there just so that people can write an
opclass name that matches the declared type of their table column.

> 2) Is there any pre-definded operator class for indexes with regular
> expressions?

No, not yet.

> 3) How do I define an index which would be invoked when querying with
> regexps in the WHERE-clause?

You don't. Alexander Korotkov is hacking on an extension to
contrib/pg_trgm that would allow probing a GIN index with trigrams
extracted from a regexp, but it's a hard problem --- he's been working
on that for many months and it's still not committed.

> 4) There appears to be no documentation of the included operator classes in
> the
> Postgresql documentation while the feature looks to me like its use cannot
> be
> emphasized enough.
> Postgresql 9.1.7 has about 220 operator classes but very few of them are
> even
> mentioned in the manual.

Most of them don't need to be, because they're the default (and usually
only) opclass for their datatype+index type anyway. The ones that are
non-default are documented in appropriate places.

regards, tom lane