Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hAu3q-0001NA-Ll for pgsql-performance@arkaria.postgresql.org; Mon, 01 Apr 2019 10:21:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hAu3o-0000G1-MR for pgsql-performance@arkaria.postgresql.org; Mon, 01 Apr 2019 10:21:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hAu3o-0000De-A1 for pgsql-performance@lists.postgresql.org; Mon, 01 Apr 2019 10:21:00 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hAu3l-00081m-B6 for pgsql-performance@postgresql.org; Mon, 01 Apr 2019 10:20:59 +0000 Received: from [192.168.127.1] (port=56873 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1hAu3f-0007wK-2Y; Mon, 01 Apr 2019 10:20:51 +0000 Received: from [127.0.0.1] (port=25825 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1hAu3e-000DQK-Aq; Mon, 01 Apr 2019 10:20:50 +0000 From: Andrew Gierth To: "tank.zhang" <6220104@qq.com> Cc: pgsql-performance@postgresql.org Subject: Re: Postgresql Sort cost Poor performance? In-Reply-To: <1554111914781-0.post@n3.nabble.com> (tank zhang's message of "Mon, 1 Apr 2019 02:45:14 -0700 (MST)") Message-ID: <87d0m6uj5u.fsf@news-spur.riddles.org.uk> References: <1554111914781-0.post@n3.nabble.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Mon, 01 Apr 2019 11:20:48 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "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. -- Andrew (irc:RhodiumToad)