On Thu, Jan 31, 2019 at 7:30 AM Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote: > > Hi Peter > > I did check out using ICU and the performance does indeed seem > comparable with C locale: > > EXPLAIN ANALYZE select * from chart order by name COLLATE "lo-x-icu"; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------- > Sort (cost=1470.65..1504.24 rows=13436 width=1203) (actual > time=82.752..85.723 rows=13436 loops=1) > Sort Key: name COLLATE "lo-x-icu" > Sort Method: quicksort Memory: 6253kB > -> Seq Scan on chart (cost=0.00..549.36 rows=13436 width=1203) > (actual time=0.043..12.634 rows=13436 loops=1) > Planning time: 1.610 ms > Execution time: 96.060 ms > (6 rows) > > The Laos folk have confirmed that the sort order with C locale was not > correct. So setting the ICU locale seems to be the way forward. > > The problem is that this is a large java application with a great > number of tables and queries. Also it is used in 60+ countries not > just Laos. So we cannot simply modify the queries or table creation > scripts directly such as in the manner above. I was hoping the > solution would just be to set a default locale on the database > (perhaps even und-x-icu) but I see now that this doesn't seem to be > currently possible with postgresql 10 ie. set the locale on database > creation to a *-icu locale. > > Is this also a limitation on postgresql 11? (Upgrading would be possible) yeah, probably. Having said that, I'm really struggling that it can take take several minutes to sort such a small number of rows even with location issues. I can sort rocks faster than that :-). Switching between various european collations, I'm seeing subsecond sort responses for 44k records on my test box. I don't have the laos collation installed unfortunately. Are you seeing kind of penalty in other conversions? merlin
