Greg Stark <gsstark@mit.edu> writes:
> John Seberg <johnseberg@yahoo.com> writes:
>> This tells me that Postgresql is not using the same
>> method for determining duplicates when GROUPING and
>> INDEXing.
> You might try running the GROUP BY query after doing:
> set enable_hashagg = false;
> With that false it would have to sort the results which should be exactly the
> same code as the index is using. I think.
If that does change the results, it indicates you've got strings which
are bytewise different but compare equal according to strcoll(). We've
seen this and other misbehaviors from some locale definitions when faced
with data that is invalid per the encoding the locale expects.
So, yeah, the answer is to fix your encoding problems. In particular,
don't ever use a locale like that with a SQL_ASCII database encoding,
because then Postgres won't prevent strcoll from seeing data it fails
on. The only safe locale setting for a SQL_ASCII database is C,
I think.
regards, tom lane