Re: How can sort performance be so different

From: Bob Jolliffe <bobjolliffe(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: How can sort performance be so different
Date: 2019-01-30 11:57:10
Message-ID: CACd=f9eV=DjLzoa6CXnHpsp5J+gDEQSJ2uH=_u4W=OZuQZ6LBA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Bob Jolliffe <bobjolliffe(at)gmail(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tumasgiu Rossini 2019-01-30 13:19:52 Re: pgstattupple vs pg_total_relation_size
Previous Message Mariel Cherkassky 2019-01-30 10:41:55 pgstattupple vs pg_total_relation_size