From: Evgeniy Shishkin <itparanoia@gmail.com>
To: Alex Ignatov <a.ignatov@postgrespro.ru>
Cc: Blas Pico <toni.pico@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query that took a lot of time in Postgresql when not using trim in order by
Date: Wed, 25 Nov 2015 19:35:15 +0300
Message-ID: <855510F3-CAF0-4C52-848E-54EC61F10DE6@gmail.com> (raw)
In-Reply-To: <5655E044.6070207@postgrespro.ru>
References: <CANL=RnsS-VFKwV0-PGRZwvV6boFRZ9CSBGvDFDm3TLts-SUHVQ@mail.gmail.com>
<5655E044.6070207@postgrespro.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> What is your Postgres version?
> Do you have correct statistics on this tables?
> Please show yours execution plans with buffers i.e. explain (analyze,buffers) ...
>
Fast:
Sort (cost=193101.41..195369.80 rows=907357 width=129) (actual time=3828.176..3831.261 rows=43615 loops=1)
Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome, (btrim((dim_cliente.tipocliente)::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=91970.52..103312.49 rows=907357 width=129) (actual time=2462.690..2496.729 rows=43615 loops=1)
Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome, btrim((dim_cliente.tipocliente)::text)
-> Hash Join (cost=856.30..80628.56 rows=907357 width=129) (actual time=29.524..1533.880 rows=907357 loops=1)
Slow:
Group (cost=170417.48..184027.84 rows=907357 width=129) (actual time=36649.329..37235.158 rows=43615 loops=1)
Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome
-> Sort (cost=170417.48..172685.88 rows=907357 width=129) (actual time=36649.315..36786.760 rows=907357 loops=1)
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_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome
Sort Method: quicksort Memory: 265592kB
-> Hash Join (cost=856.30..80628.56 rows=907357 width=129) (actual time=26.719..1593.693 rows=907357 loops=1)
The difference is in the top of plans.
As we see, hashjoin time is practically the same.
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: itparanoia@gmail.com, a.ignatov@postgrespro.ru, toni.pico@gmail.com
Subject: Re: Query that took a lot of time in Postgresql when not using trim in order by
In-Reply-To: <855510F3-CAF0-4C52-848E-54EC61F10DE6@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox