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@sss.pgh.pa.us> wrote:
>
> Bob Jolliffe <bobjolliffe@gmail.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