Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a1d2V-0004Ow-6Q for pgsql-performance@arkaria.postgresql.org; Wed, 25 Nov 2015 16:35:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a1d2U-0005uF-KK for pgsql-performance@arkaria.postgresql.org; Wed, 25 Nov 2015 16:35:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1d2T-0005tr-Ne for pgsql-performance@postgresql.org; Wed, 25 Nov 2015 16:35:25 +0000 Received: from mail-lf0-x232.google.com ([2a00:1450:4010:c07::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1a1d2R-0002FM-4T for pgsql-performance@postgresql.org; Wed, 25 Nov 2015 16:35:24 +0000 Received: by lfaz4 with SMTP id z4so67275139lfa.0 for ; Wed, 25 Nov 2015 08:35:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=lfVN5p0xrlsktD6qobORfoON1Ml1+WtzYSutiOyQk3I=; b=T71maIrdfwD3nI/U5e+mTOx3WriYZP8nbOJKsDrzQv3o0+//YbNB9PcKGFyc6mdjQG u1IYWmPozOt6E2OxhflIcVQc0ypcaKizbb+r6+qVup8Z57WJI8+/BHoRG8RnbZrX3Z4m sEsOvhGo0qnrMBdGswudZszWus7BVHzWEDJi1lJy5vaQjBJ/SRnNEl/N3LHZCYvdrb62 pIG7CJGXXhFxaOSLQCRxx0VxDxrku48CU4E+bZfID161BreraN7ZApND9CWO27udTVIe jg/j5fHg1Zh9b2ZSclxVd1I7d3YKlgE7EszHI0LajyakOrUvqROgfhxdYaZj5YzVseK4 oZAA== X-Received: by 10.25.153.146 with SMTP id b140mr16209733lfe.33.1448469320937; Wed, 25 Nov 2015 08:35:20 -0800 (PST) Received: from wgaybook.corp.wargaming.local (by1-office.wargaming.net. [178.172.239.243]) by smtp.gmail.com with ESMTPSA id k189sm3429195lfd.12.2015.11.25.08.35.19 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Wed, 25 Nov 2015 08:35:20 -0800 (PST) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 9.1 \(3096.5\)) Subject: Re: Query that took a lot of time in Postgresql when not using trim in order by From: Evgeniy Shishkin In-Reply-To: <5655E044.6070207@postgrespro.ru> Date: Wed, 25 Nov 2015 19:35:15 +0300 Cc: Blas Pico , pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <855510F3-CAF0-4C52-848E-54EC61F10DE6@gmail.com> References: <5655E044.6070207@postgrespro.ru> To: Alex Ignatov X-Mailer: Apple Mail (2.3096.5) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org > What is your Postgres version? > Do you have correct statistics on this tables? > Please show yours execution plans with buffers i.e. explain (analyze,buf= fers) ... >=20 Fast: Sort (cost=3D193101.41..195369.80 rows=3D907357 width=3D129) (actual time= =3D3828.176..3831.261 rows=3D43615 loops=3D1) Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod= , dim_cliente.a1_nome, dim_vendedor.a3_nome, (btrim((dim_cliente.tipoclient= e)::text)) Sort Key: (btrim((dim_cliente.tipocliente)::text)), dim_cliente.a1_ibge,= dim_cliente.a1_cod, dim_cliente.a1_nome Sort Method: quicksort Memory: 13121kB -> HashAggregate (cost=3D91970.52..103312.49 rows=3D907357 width=3D129= ) (actual time=3D2462.690..2496.729 rows=3D43615 loops=3D1) Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.= a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome, btrim((dim_cliente.tipoc= liente)::text) -> Hash Join (cost=3D856.30..80628.56 rows=3D907357 width=3D129)= (actual time=3D29.524..1533.880 rows=3D907357 loops=3D1) Slow: Group (cost=3D170417.48..184027.84 rows=3D907357 width=3D129) (actual tim= e=3D36649.329..37235.158 rows=3D43615 loops=3D1) Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod= , dim_cliente.a1_nome, dim_vendedor.a3_nome -> Sort (cost=3D170417.48..172685.88 rows=3D907357 width=3D129) (actua= l time=3D36649.315..36786.760 rows=3D907357 loops=3D1) Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.= a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome Sort Key: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_client= e.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome Sort Method: quicksort Memory: 265592kB -> Hash Join (cost=3D856.30..80628.56 rows=3D907357 width=3D129)= (actual time=3D26.719..1593.693 rows=3D907357 loops=3D1) The difference is in the top of plans. As we see, hashjoin time is practically the same.=20 But fast plan uses hashagg first and only 43k rows require sorting. Slow plan dominated by sorting 900k rows. I wonder if increasing cpu_tuple_cost will help. As cost difference between two plans is negligible now. =20=20=20=20 --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance