Thread: Question about indexes and operator classes
Hi,
I actually have several questions on indexes and operator classes:
1) What is the difference between varchar_pattern_ops and varchar_text_ops?
They both appear to work similarly and in both cases the operands are cast to
type TEXT when I look at the explain of the query.
2) Is there any pre-definded operator class for indexes with regular expressions?
3) How do I define an index which would be invoked when querying with regexps
in the WHERE-clause?
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. Oracle 11 apparently has no pre-defined operator classes
(they call them "application domain indexes") and only includes a documentation
on how to make them which is more or less around three corners and climbing stairs
while balancing a water cup on your nose - this appears to me to be a very big marketing advantage for Postgres over Oracle.
Should I post a bug report to the PGSQL-BUGS list against the documentation ?
Thanks,
R.
ERR ORR <rd0002@gmail.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