Re: How can sort performance be so different

Sorry Merlin for not replying earlier. The difference is indeed hard
to understand but it is certainly there. We altered the collation to
use on the name field in that table and the problem has gone. Having
having solved the immediate problem we haven't investigated much
further yet.

Not sure what exactly you mean by "other conversions"?

On Tue, 5 Feb 2019 at 20:28, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> 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