If you are going to be only doing case-insensitive compares, why would you
have two indexes on the field?
Although I am no guru on PostgreSQL internals or database theory, a
case insensitive select on a mixed case index would not work for the
following reason (correct me if i am wrong):
1) becuase of ASCII values and the way btree indexes are ordered, 'A' and
'a' are not store next to each other, meaning that you cannot map all the
caracters of the index to the same case on the fly w/o missing a chunk of
index (unless you wanted to make multiple passes through the index, which
would negate any speed gains of *not* having multiple indexes becuase of
the exponential growth, i.e. searching for 'that' would require 16 passes
thru -- what, What, wHat, whAt, whaT, etc.)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 15 Feb 2001, David Wheeler wrote:
>
> On Thu, 15 Feb 2001, Michael Fork wrote:
>
> > Indexes *can* and *will* be used if you create the appropiate
> > functional indexes, i.e:
> >
> > CREATE INDEX idx_table_field_upper ON table(upper(field));
> >
> > SELECT field FROM table WHERE upper(field) LIKE upper('some string');
>
> Hmmm...I'd hate to have two indexes on every field I query like this, one
> case-senstive, one case-insensitve (like the one you create here). Is
> there a configuration option or something that will tell pgsql to do
> case-insensitive comparisons (kinda like MS SQL Server has)? That could
> save us on indexing overhead, since we want all of our WHERE comparisons
> to be case-insensitive, anyway.
>
> I should also not that we're also using --with-multibyte and having all of
> our databases use Unicode exclusively.
>
> Thanks!
>
> David
>