Daniel Kalchev <daniel@digsys.bg> writes:
> 1. LIKE with indexes works worse than without indexes.
Since you are using USE_LOCALE, the parser is inserting only a one-sided
index restriction; that isWHERE w_key like 'sometext%'
becomesWHERE w_key like 'sometext%' AND w_key >= 'sometext'
whereas without USE_LOCALE it becomesWHERE w_key like 'sometext%' AND w_key >= 'sometext' AND w_key <=
'sometext\377'
6.4 always did the latter, which was wrong in non-ASCII locales because
\377 might not be the highest character in the sort order. (Strictly
speaking it's wrong in ASCII locale as well...)
Of course, the one-sided index restriction is much less selective than
the two-sided; depending on what 'sometext' actually is, you might end
up scanning most of the table, and since index scan is much slower
per-tuple-scanned than sequential scan, you lose. That's evidently
what's happening here.
I suspect that the optimizer's cost estimates need refinement;
it should be able to guess that the sequential scan will be the
faster choice here.
Of course what you really want is a two-sided index restriction,
but we are not going to be able to fix that until someone figures
out a locale-independent way of generating a "slightly larger"
comparison string. So far I have not heard any proposals that
sound like they will work...
> Under Postgres 6.5 hwoever, it
> is not accepted, because there are no aggregates in the target list.
No, that's not what it's unhappy about; it's unhappy because there
are ungrouped fields used in the target list. This is erroneous
SQL because there's no unique choice of value to return for such an
field (if several tuples are grouped together, which one's value
of the field do you use?) Prior versions of Postgres failed to detect
this error, but it's an error. You were getting randomly selected
values for the ungrouped fields, I suppose.
regards, tom lane