I'm a little frustrated
select * from mytable where mystring = 'foo';
Uses an index
select * from mytable where mystring like 'foo';
Does not use an index.
I know Tom is not to excited about this, but I think it is a serious
problem. What really brings me to this is that I just installed 7.4.2. It
is my first real deployment of PostgreSQL in about a year and a half.
Unknown to me, the default for my latest DB was not type 'C' but
"en_US.iso885915" and thus no amount of work would have allowed a 'LIKE'
to use an index without surrounding the index and query with some
function, like lower(). This "upgrade" seriously broke a working
installation.
In the foggy recesses of my mind, I vaguely recalled locale issues with
various non-english languages. This shouldn't have been a problem as I
was, I thought, just using the default. Surprisingly, SHOW ALL, showed
differently. I recreate the database with --no-locale, then it works,
obviously.
Yea, this amounts to an RTFM issue, granted, but shouldn't various locales
be able to work with LIKE? Shouldn't "en_US.iso885915" work with "LIKE?"
Shouldn't database creation with anything but 'C' issue a warning?
The real issue here is that one has to know that the behavior of "LIKE" is
dependent on the locale to understand the problem. Yes it is briefly
mentioned in the FAQ, but it is not obvious as a common problem in the
UNIX world. As far as I can tell it is a PostgreSQL only issue that the
locale setting in the system seriously affects functionality.
It is further compounded by the fact that this setting can not be changed
without recreating the database. Given a non-trivally sized database, this
is no small issue.
(Don't get me wrong, these RTFM landmines are great for the consultant and
support industry, keep up the good work ;-))