Re: Postgresql Sort cost Poor performance? - Mailing list pgsql-performance

From Gavin Flower
Subject Re: Postgresql Sort cost Poor performance?
Date
Msg-id 3113f2f4-a4da-4862-8596-2e189398c988@archidevsys.co.nz
Whole thread Raw
In response to Re: Postgresql Sort cost Poor performance?  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Responses Re: Postgresql Sort cost Poor performance?  (Christoph Berg <myon@debian.org>)
List pgsql-performance
On 01/04/2019 23:20, Andrew Gierth wrote:
>>>>>> "tank" == tank zhang <6220104@qq.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




pgsql-performance by date:

Previous
From: "tank.zhang"
Date:
Subject: Re: Postgresql Sort cost Poor performance?
Next
From: Christoph Berg
Date:
Subject: Re: Postgresql Sort cost Poor performance?