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