Re: How can sort performance be so different

Hi Tom

After much performance measuring of VPS I believe you are right in
your suspicion about locale.

The database is full of Laos characters (it is a government system in
Laos). When I tested on my VPS (en_US.UTF-8) I get the crazy slow
performance, whereas my laptop postgresql is C.UTF-8.

Modifying the query from :

explain analyze select * from chart order by name;

to

explain analyze select * from chart order by name COLLATE "C";

and the same query runs like a rocket. Amazing, yes 1000 times faster.

What I don't know yet is
(i) whether the sort order makes sense for the Laos names; and
(ii) what the locale settings are on the production server where the
problem was first reported.

There will be some turnaround before I get this information. I am
guessing that the database is using "en_US" rather than anything Laos
specific. In which case "C" would probably be no worse re sort order.
But will know better soon.

This has been a long but very fruitful investigation. Thank you all for input.

Regards
Bob

On Tue, 29 Jan 2019 at 18:47, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Bob Jolliffe <bobjolliffe(at)gmail(dot)com> writes:
> > I wonder what can cause such a massive discrepancy in the sort time.
>
> Are you using the same locale (LC_COLLATE) setting on both machines?
> Some locales sort way slower than C locale does. That's not enough
> to explain a 1000X discrepancy --- I concur with the other opinions
> that there's something wrong with your VPS --- but it might account
> for something like 10X of it.
>
> regards, tom lane