On 25.11.2015 17:15, Blas Pico wrote:

I have a query that produce a different query plan if I put a trim in one of the columns in the order by.


When i put the trim in any column it use hashaggregate and took 3 seconds against 30 when not.


Is wear because the columns is clean not need to be trimmed, I have check that.


The problem is that I can't change the query because is generate by the mondrian.


I do research and found in postgres list that I need to crank work_mem up high but don't work for me.



My postgresql.conf


# Add settings for extensions here


default_statistics_target = 50 # pgtune wizard 2014-06-04


maintenance_work_mem = 1GB # pgtune wizard 2014-06-04


constraint_exclusion = on # pgtune wizard 2014-06-04


checkpoint_completion_target = 0.9 # pgtune wizard 2014-06-04


effective_cache_size = 44GB # pgtune wizard 2014-06-04


work_mem = 1536MB # pgtune wizard 2014-06-04


#work_mem = 16GB # I have try this but don't work


wal_buffers = 32MB # pgtune wizard 2014-06-04


checkpoint_segments = 16 # pgtune wizard 2014-06-04


shared_buffers = 15GB # pgtune wizard 2014-06-04


max_connections = 20 # pgtune wizard 2014-06-04




___________________________________________________




Query with trim


SELECT "dim_cliente"."tipocliente" AS "c0",


 "dim_cliente"."a1_ibge" AS "c1",


 "dim_cliente"."a1_cod" AS "c2",


 "dim_cliente"."a1_nome" AS "c3",


 "dim_vendedor"."a3_nome" AS "c4"


  FROM "public"."dim_cliente" AS "dim_cliente",


 "public"."fato_ventas_productos" AS "fato_ventas_productos",


 "public"."dim_vendedor" AS "dim_vendedor"


WHERE "fato_ventas_productos"."key_cliente" = "dim_cliente"."key_cliente"


AND "fato_ventas_productos"."key_vendedor" = "dim_vendedor"."key_vendedor"


 GROUP


    BY "dim_cliente"."tipocliente" ,


 "dim_cliente"."a1_ibge",


 "dim_cliente"."a1_cod",


 "dim_cliente"."a1_nome",


 "dim_vendedor"."a3_nome"


 ORDER


    BY trim("dim_cliente"."tipocliente") ASC NULLS LAST,


"dim_cliente"."a1_ibge" ASC NULLS LAST, -- the same result if I put the trim here


 "dim_cliente"."a1_cod" ASC NULLS LAST, -- or here


 "dim_cliente"."a1_nome" ASC NULLS LAST; -- or here


-- this query took 3845.895 ms




___________________________________________________




Query Plan when using trim


   QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------


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)


Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome


Hash Cond: (fato_ventas_productos.key_vendedor = dim_vendedor.key_vendedor)


-> Hash Join (cost=830.02..68126.13 rows=907357 width=86) (actual time=28.746..1183.691 rows=907357 loops=1)


Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, fato_ventas_productos.key_vendedor


Hash Cond: (fato_ventas_productos.key_cliente = dim_cliente.key_cliente)


-> Seq Scan on public.fato_ventas_productos (cost=0.00..46880.57 rows=907357 width=16) (actual time=0.004..699.779 rows=907357 loops=1)


Output: fato_ventas_productos.key_cliente, fato_ventas_productos.key_vendedor


-> Hash (cost=618.90..618.90 rows=16890 width=86) (actual time=28.699..28.699 rows=16890 loops=1)


Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_cliente.key_cliente


           Buckets: 2048  Batches: 1  Memory Usage: 1980kB


-> Seq Scan on public.dim_cliente (cost=0.00..618.90 rows=16890 width=86) (actual time=0.008..16.537 rows=16890 loops=1)


Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_cliente.key_cliente


-> Hash (cost=18.90..18.90 rows=590 width=59) (actual time=0.747..0.747 rows=590 loops=1)


     Output: dim_vendedor.a3_nome, dim_vendedor.key_vendedor


     Buckets: 1024  Batches: 1  Memory Usage: 56kB


-> Seq Scan on public.dim_vendedor (cost=0.00..18.90 rows=590 width=59) (actual time=0.026..0.423 rows=590 loops=1)


           Output: dim_vendedor.a3_nome, dim_vendedor.key_vendedor


 Total runtime: 3845.895 ms


(25 filas)



___________________________________________________


Query without trim

SELECT "dim_cliente"."tipocliente" AS "c0",

 "dim_cliente"."a1_ibge" AS "c1",

 "dim_cliente"."a1_cod" AS "c2",

 "dim_cliente"."a1_nome" AS "c3",

 "dim_vendedor"."a3_nome" AS "c4"

  FROM "public"."dim_cliente" AS "dim_cliente",

 "public"."fato_ventas_productos" AS "fato_ventas_productos",

 "public"."dim_vendedor" AS "dim_vendedor"

WHERE "fato_ventas_productos"."key_cliente" = "dim_cliente"."key_cliente"

AND "fato_ventas_productos"."key_vendedor" = "dim_vendedor"."key_vendedor"

 GROUP

    BY "dim_cliente"."tipocliente" ,

 "dim_cliente"."a1_ibge",

 "dim_cliente"."a1_cod",

 "dim_cliente"."a1_nome",

 "dim_vendedor"."a3_nome"

 ORDER

    BY "dim_cliente"."tipocliente" ASC NULLS LAST,

 "dim_cliente"."a1_ibge" ASC NULLS LAST,

 "dim_cliente"."a1_cod" ASC NULLS LAST,

 "dim_cliente"."a1_nome" ASC NULLS LAST;

-- this query took 37249.268 ms


___________________________________________________


Query Plan when not using trim

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------

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)

Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_vendedor.a3_nome

Hash Cond: (fato_ventas_productos.key_vendedor = dim_vendedor.key_vendedor)

-> Hash Join (cost=830.02..68126.13 rows=907357 width=86) (actual time=25.980..1203.775 rows=907357 loops=1)

Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, fato_ventas_productos.key_vendedor

Hash Cond: (fato_ventas_productos.key_cliente = dim_cliente.key_cliente)

-> Seq Scan on public.fato_ventas_productos (cost=0.00..46880.57 rows=907357 width=16) (actual time=0.004..680.283 rows=907357 loops=1)

Output: fato_ventas_productos.key_cliente, fato_ventas_productos.key_vendedor

-> Hash (cost=618.90..618.90 rows=16890 width=86) (actual time=25.931..25.931 rows=16890 loops=1)

Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_cliente.key_cliente

           Buckets: 2048  Batches: 1  Memory Usage: 1980kB

-> Seq Scan on public.dim_cliente (cost=0.00..618.90 rows=16890 width=86) (actual time=0.005..13.736 rows=16890 loops=1)

Output: dim_cliente.tipocliente, dim_cliente.a1_ibge, dim_cliente.a1_cod, dim_cliente.a1_nome, dim_cliente.key_cliente

-> Hash (cost=18.90..18.90 rows=590 width=59) (actual time=0.715..0.715 rows=590 loops=1)

     Output: dim_vendedor.a3_nome, dim_vendedor.key_vendedor

     Buckets: 1024  Batches: 1  Memory Usage: 56kB

-> Seq Scan on public.dim_vendedor (cost=0.00..18.90 rows=590 width=59) (actual time=0.024..0.405 rows=590 loops=1)

           Output: dim_vendedor.a3_nome, dim_vendedor.key_vendedor

 Total runtime: 37249.268 ms

(25 filas)


___________________________________________________


Is anything that I can do to solve this problem, is that a bug or a config problem?


Here the link with a dump of the tables

https://drive.google.com/file/d/0Bwupj61i9BtWZ1NiVXltaWc0dnM/view?usp=sharing


I appreciate your help

Hello!
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) ...

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Reply via email to