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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general