Re: is it normal behavior of index?

Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:

> On Sun, 1 Jan 2012 14:11:11 +0100
> Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:
>
> >
> > select *, without a WHERE-condition. In this case an index is useless,
> > the whole table is the result and a seq-scan the fastest way.
>
> I *need* it to work to present ordered lists!
> And I don't agree, essentially because of that:

I think, in this case you needs 2 indexes, one with and one without the
opclass - option.

Copy&Paste from
http://www.postgresql.org/docs/9.1/static/indexes-opclass.html:

The operator classes text_pattern_ops, varchar_pattern_ops, and
bpchar_pattern_ops support B-tree indexes on the types text, varchar,
and char respectively. The difference from the default operator classes
is that the values are compared strictly character by character rather
than according to the locale-specific collation rules. This makes these
operator classes suitable for use by queries involving pattern matching
expressions (LIKE or POSIX regular expressions) when the database does
not use the standard "C" locale. As an example, you might index a
varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

Note that you should also create an index with the default operator
class if you want queries involving ordinary <, <=, >, or >= comparisons
to use an index. Such queries cannot use the xxx_pattern_ops operator
classes.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°