Re: Postgresql Sort cost Poor 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