Date: Sun, 29 Nov 2015 14:23:23 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-performance@postgresql.org
Subject: Re: Query that took a lot of time in Postgresql when not
 using trim in order by
Message-ID: <20151129132323.GA29654@hjp.at>
References: 
 <CANL=RnsS-VFKwV0-PGRZwvV6boFRZ9CSBGvDFDm3TLts-SUHVQ@mail.gmail.com>
 <5655E044.6070207@postgrespro.ru>
 <855510F3-CAF0-4C52-848E-54EC61F10DE6@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="HlL+5n6rz5pIUxbD"
Content-Disposition: inline
In-Reply-To: <855510F3-CAF0-4C52-848E-54EC61F10DE6@gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--HlL+5n6rz5pIUxbD
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2015-11-25 19:35:15 +0300, Evgeniy Shishkin wrote:
> Fast:
>=20
>  Sort  (cost=3D193101.41..195369.80 rows=3D907357 width=3D129) (actual ti=
me=3D3828.176..3831.261 rows=3D43615 loops=3D1)
>    Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_c=
od, dim_cliente.a1_nome, dim_vendedor.a3_nome, (btrim((dim_cliente.tipoclie=
nte)::text))
>    Sort Key: (btrim((dim_cliente.tipocliente)::text)), dim_cliente.a1_ibg=
e, dim_cliente.a1_cod, dim_cliente.a1_nome
>    Sort Method: quicksort  Memory: 13121kB
>    ->  HashAggregate  (cost=3D91970.52..103312.49 rows=3D907357 width=3D1=
29) (actual time=3D2462.690..2496.729 rows=3D43615 loops=3D1)
>          Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_client=
e.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome, btrim((dim_cliente.tip=
ocliente)::text)
>          ->  Hash Join  (cost=3D856.30..80628.56 rows=3D907357 width=3D12=
9) (actual time=3D29.524..1533.880 rows=3D907357 loops=3D1)
>=20
>=20
> Slow:
>=20
>  Group  (cost=3D170417.48..184027.84 rows=3D907357 width=3D129) (actual t=
ime=3D36649.329..37235.158 rows=3D43615 loops=3D1)
>    Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_c=
od, dim_cliente.a1_nome, dim_vendedor.a3_nome
>    ->  Sort  (cost=3D170417.48..172685.88 rows=3D907357 width=3D129) (act=
ual time=3D36649.315..36786.760 rows=3D907357 loops=3D1)
>          Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_client=
e.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome
>          Sort Key: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_clie=
nte.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=3D12=
9) (actual time=3D26.719..1593.693 rows=3D907357 loops=3D1)
>=20
>=20
> 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.
>=20
> I wonder if increasing cpu_tuple_cost will help.
> As cost difference between two plans is negligible now.

Seems plausible. Also I'm wondering what CPU this is: 36 seconds for an
in-memory sort of 900k rows seems slow to me. I tested this on my PC at
home (1.8 GHz Core2 Dual, so a rather old and slow box) and I could sort
1E6 rows of 128 random bytes in 5.6 seconds. Even if I kept the first 96
bytes constant (so only the last 32 were random), it took only 21
seconds. Either this CPU is really slow or the data is heavily skewed -
is it possible that all dimensions except dim_vendedor.a3_nome have only
one or very few values? In that case changing the sort order might help.

	hp


--=20
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants=20
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/

--HlL+5n6rz5pIUxbD
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIVAwUBVlr8SvIOSFES/ihdAQL/4A//cFnfSG1GK85bjhVCMrvF13nzjif1lYqi
B3FaFsHuc4z61PtdGxhnP4ksNWFPyNksxVHaN81e2DcgCTPplL+Q+qt/sJSNFl0M
xW6AbY5pLuo1dp3IakBPJqlt4RX3pDW72A0ZOngvkKr6ngaWK+lxb3T65vcGL8up
1QqbVtZDjDUSODwBEf8634hfUFqopbgDiauwG+S4GFo2SSp00ErrlQAlUTBPgHR6
GXp+zMFUHCzkhI57ruVPLO1qC+BRxCPhdI8+HakRFAk6bAYT6f/7HEgtLJFut6PG
7CR8yt49guKhcOS4Wo6P9tVPuTAtrbN22dB4EfPYDWfX6hUMwPWqxQgFrOqm7vZ5
Uzb3RUrm1RgLZkOUqLCbE+ddqIoeov4JuqJXLhsxGPnShyEhjTwHPcHxvCcn42ui
KJ70YPrThdVpIRY1A9XzLZ3px0vRxwEGYsSUq8g635XL4sU/lgjOV/OkO0EKEk+7
hnNufIAri7kcmsj6ILsmxhyqqLnf10Qhpr9xmAhB7Xp43kcq45+/qw4AeqUPXP5S
Ey0dBJsV00Gv0siWCuOxjJqYpM767/maB+Ubkt6oDEM8Z+g308ThsJ5g9BJWh/nk
v7vaL2YaP96mgNsOUMJdyQkBUuhPLCYuoFt96q0nib6tNWKsPtCB1JuNTDcmooux
A2QIrjoeOjk=
=70QS
-----END PGP SIGNATURE-----

--HlL+5n6rz5pIUxbD--