On 01/04/2019 23:20, Andrew Gierth wrote:
"tank" == tank zhang <6220104@xxxxxx> 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
