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
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 |