From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Postgresql Sort cost Poor performance? |
Date: | 2019-04-01 10:32:40 |
Message-ID: | 3113f2f4-a4da-4862-8596-2e189398c988@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 01/04/2019 23:20, Andrew Gierth wrote:
>>>>>> "tank" == tank zhang <6220104(at)qq(dot)com> writes:
> tank> smtoc | character varying(50) | | |
> tank> Sort Key: smtoc
>
> What is the output of SHOW lc_collate;
>
> One of the most common reasons for slow sorting is that you're sorting a
> text/varchar field in a locale other than C. The slowdown for using
> other locales varies according to the data, the locale, and the
> operating system, but 8-20x slowdowns are very common, 50-100x slowdowns
> are not unusual, and there have been reports of even worse cases with
> unusual script combinations.
>
Just wondering...
Would it be possible to optionally enable the system to create a hidden
system column for the text field to be sorted, the new column would be
the original column preprocessed to sort correctly & efficiently. This
would seem to lead to a massive improvement in performance.
Depending relative tradeoffs disk storage vs processing:
(A) create hidden system column for each sort invocation
(B) at table creation
(C) other possibilities
(A) could be done automatically, and possibly controlled via a GUC parameter
(B) might require a change to the CREATE TABLE syntax
Anyhow, just some thoughts...
Cheers,
Gavin
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Berg | 2019-04-01 10:54:35 | Re: Postgresql Sort cost Poor performance? |
Previous Message | tank.zhang | 2019-04-01 10:30:09 | Re: Postgresql Sort cost Poor performance? |