Re: How can sort performance be so different

On Thu, Jan 31, 2019 at 7:30 AM Bob Jolliffe <bobjolliffe(at)gmail(dot)com> 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