pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow HashAggregate/cache access
13+ messages / 5 participants
[nested] [flat]
* Slow HashAggregate/cache access
@ 2015-08-05 01:41 Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 17:24 ` Re: Slow HashAggregate/cache access Kevin Grittner <kgrittn@ymail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Alexandre de Arruda Paes @ 2015-08-05 01:41 UTC (permalink / raw)
To: pgsql-performance
Hi,
First, sorry to compare Post with other database system, but I know nothing
about Oracle...
This customer have an application made with a framework thats generates the
SQL statements (so, We can't make any query optimizations) .
We did the following tests:
1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
disk,Core i5)
2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS
disks)
In the first machine, postgresql takes from 20,000 to 40,000 ms to complete
the query and from 1,200 to 2,000 ms in the others runs. Oracle in this
machine takes 2,000ms in the first run and *70ms* using cache.
In the second machine, postgresql takes about 2,000ms in the first run and
about 800ms in the others. 11x slow than Oracle times, in a much more
powefull machine.
Bellow is the 2 explains in the second server:
database=# explain (analyze,buffers) SELECT
T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS
fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
T1.fr02codigo,T1.fr01codemp FROM((((FR13T T1 LEFT JOIN FR02T T2 ON
T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >=
'01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999')
AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=30535.97..33804.07 rows=1 width=130) (actual
time=1371.548..1728.058 rows=2 loops=1)
Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 368
Buffers: shared hit=95 read=21267
-> Nested Loop Left Join (cost=30529.83..33796.84 rows=1 width=98)
(actual time=1345.565..1701.990 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=95 read=21265
-> Nested Loop Left Join (cost=30529.70..33796.67 rows=1
width=87) (actual time=1340.393..1696.793 rows=2 loops=1)
Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 500202
Buffers: shared hit=93 read=21263
-> Nested Loop Left Join (cost=0.70..2098.42 rows=1
width=23) (actual time=36.424..66.841 rows=2 loops=1)
Buffers: shared hit=93 read=88
-> Index Scan using ufr13t2 on fr13t t1
(cost=0.42..2094.11 rows=1 width=19) (actual time=27.518..57.910 rows=2
loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
Filter: ((fr02codigo >= 0::numeric) AND
(fr02codigo <= 9999999999::numeric) AND (fr13codpr = 60732))
Rows Removed by Filter: 5621
Buffers: shared hit=90 read=85
-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=4.455..4.458 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=3 read=3
-> HashAggregate (cost=30529.00..30840.80 rows=31180
width=21) (actual time=630.594..753.406 rows=250102 loops=2)
Buffers: shared read=21175
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=6.354..720.037 rows=311800 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared read=21175
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
rows=1 width=15) (actual time=2.584..2.586 rows=1 loops=2)
Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=2 read=2
-> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
time=12.906..12.972 rows=184 loops=2)
Buffers: shared read=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=25.570..25.624 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared read=2
Total runtime: 1733.320 ms
(35 rows)
database=# explain (analyze,buffers) SELECT
T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS
fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
T1.fr02codigo,T1.fr01codemp FROM((((FR13T T1 LEFT JOIN FR02T T2 ON
T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >=
'01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999')
AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=30535.97..33804.07 rows=1 width=130) (actual
time=492.669..763.313 rows=2 loops=1)
Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 368
Buffers: shared hit=21362
-> Nested Loop Left Join (cost=30529.83..33796.84 rows=1 width=98)
(actual time=492.462..763.015 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=21360
-> Nested Loop Left Join (cost=30529.70..33796.67 rows=1
width=87) (actual time=492.423..762.939 rows=2 loops=1)
Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 500202
Buffers: shared hit=21356
-> Nested Loop Left Join (cost=0.70..2098.42 rows=1
width=23) (actual time=0.855..2.268 rows=2 loops=1)
Buffers: shared hit=181
-> Index Scan using ufr13t2 on fr13t t1
(cost=0.42..2094.11 rows=1 width=19) (actual time=0.844..2.229 rows=2
loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
Filter: ((fr02codigo >= 0::numeric) AND
(fr02codigo <= 9999999999::numeric) AND (fr13codpr = 60732))
Rows Removed by Filter: 5621
Buffers: shared hit=175
-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=0.009..0.012 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=6
-> HashAggregate (cost=30529.00..30840.80 rows=31180
width=21) (actual time=229.435..325.660 rows=250102 loops=2)
Buffers: shared hit=21175
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=0.003..74.088 rows=311800 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared hit=21175
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
rows=1 width=15) (actual time=0.023..0.024 rows=1 loops=2)
Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=4
-> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
time=0.065..0.098 rows=184 loops=2)
Buffers: shared hit=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=0.006..0.029 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared hit=2
Total runtime: 763.536 ms
(35 rows)
Thanks for any help.
Best regards,
Alexandre
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
@ 2015-08-05 17:24 ` Kevin Grittner <kgrittn@ymail.com>
2015-08-05 19:29 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Kevin Grittner @ 2015-08-05 17:24 UTC (permalink / raw)
To: Alexandre de Arruda Paes <adaldeia@gmail.com>; pgsql-performance
Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
> We did the following tests:
>
> 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA disk,Core i5)
> 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS disks)
That's only part of the information we would need to be able to
give specific advice. Please read this page:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
One possibility is that you are running with the default
configuration, rather than having tuned for the hardware. You are
very likely to need to adjust shared_buffers, effective_cache_size,
work_mem, maintenance_work_mem, random_page_cost, cpu_tuple_cost,
and (at least for the second machine) effective_io_concurrency. If
the queries have a lot of joins you may need to increase
from_collapse_limit and/or join_collapse_limit. You also may need
to adjust [auto]vacuum and/or background writer settings. Various
OS settings may matter, too.
To get a handle on all this, it might be worth looking for Greg
Smith's book on PostgreSQL high performance.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 17:24 ` Re: Slow HashAggregate/cache access Kevin Grittner <kgrittn@ymail.com>
@ 2015-08-05 19:29 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Alexandre de Arruda Paes @ 2015-08-05 19:29 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Hi,
Kevin:
Second machine config parameters:
shared_buffers = 8GB
work_mem = 1 GB (was 512MB)
maintenace_work_mem = 4 GB
#seq_page_cost = 1.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
random_page_cost = 2.0
effective_cache_size = 110GB
I try to change from_collapse_limit, join_collapse_limit and io_con, w/o
success.
I create a database with this tables only, vaccum analyze them and test
with only my connection to postgresql.
Now we have another querys(all with aggregates) that the time is 15x - 20x
slower than Oracle and SQL Server.
All tables have indexes (btree) with fields in the where/order/group
parameters.
Maxim:
The developer is changing from a Desktop application (ODBC with Use
Declare/Fetch, 'single' querys with local summing and aggregation) for a
client/server web application (.NET, most querys with aggregate). Unfortunattly
we cant change this querys, but I will try your solution to see what
happens.
Take a look at another big query generated by the development tool.
Oracle/SQL Server runs the same query (with the same data but in a slow
machine) in about 2 seconds:
http://explain.depesz.com/s/wxq
Best regards,
Alexandre
2015-08-05 14:24 GMT-03:00 Kevin Grittner <kgrittn@ymail.com>:
> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>
> > We did the following tests:
> >
> > 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
> disk,Core i5)
> > 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores,
> SAS disks)
>
> That's only part of the information we would need to be able to
> give specific advice. Please read this page:
>
> https://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> One possibility is that you are running with the default
> configuration, rather than having tuned for the hardware. You are
> very likely to need to adjust shared_buffers, effective_cache_size,
> work_mem, maintenance_work_mem, random_page_cost, cpu_tuple_cost,
> and (at least for the second machine) effective_io_concurrency. If
> the queries have a lot of joins you may need to increase
> from_collapse_limit and/or join_collapse_limit. You also may need
> to adjust [auto]vacuum and/or background writer settings. Various
> OS settings may matter, too.
>
> To get a handle on all this, it might be worth looking for Greg
> Smith's book on PostgreSQL high performance.
>
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
@ 2015-08-05 18:25 ` Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 19:55 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
2015-08-05 22:07 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
1 sibling, 2 replies; 13+ messages in thread
From: Maxim Boguk @ 2015-08-05 18:25 UTC (permalink / raw)
To: Alexandre de Arruda Paes <adaldeia@gmail.com>; +Cc: pgsql-performance
On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <
adaldeia@gmail.com> wrote:
> Hi,
>
> First, sorry to compare Post with other database system, but I know
> nothing about Oracle...
>
> This customer have an application made with a framework thats generates
> the SQL statements (so, We can't make any query optimizations) .
>
> We did the following tests:
>
> 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
> disk,Core i5)
> 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS
> disks)
>
>
> database=# explain (analyze,buffers)
>
> SELECT T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0)
> AS fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
> fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
> T1.fr02codigo,T1.fr01codemp FROM((((FR13T T1 LEFT JOIN FR02T T2 ON
> T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
> FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
> JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
> fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
> NUMERIC(18,10))) AS fr13VrTot
> FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
> T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
> T4.fr13dtlanc = T1.fr13dtlanc)
>
> LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
> fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
> T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
> T5.fr13dtlanc = T1.fr13dtlanc)
> WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >=
> '01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999')
> AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
> T1.fr13dtlanc;
>
>
I think I know where issue is.
The PostgreSQL planner unable pass join conditions into subquery with
aggregate functions (it's well known limitation).
For sample to calculate this part:
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
PostgreSQL forced to calculate full aggregate subquery, instead of pass
JOIN conditions into it.
I suggest rewrite query to the following form:
SELECT T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0)
AS fr13TotQtd,T1.fr13codpr,T1.fr13categ,
(SELECT SUM(fr13VrBx) FROM FR13T3 AS T5 WHERE T5.fr01codemp = T1.fr01codemp
AND T5.fr02codigo = T1.fr02codigo AND T5.fr13dtlanc = T1.fr13dtlanc) AS
fr13TotBx,
(SELECT SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot FROM FR13T1 AS T4 WHERE T4.fr01codemp =
T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND T4.fr13dtlanc =
T1.fr13dtlanc) AS fr13VrTot,
T2.fr09cod, T3.fr09desc, T1.fr02codigo,T1.fr01codemp
FROM
FR13T T1 LEFT JOIN FR02T T2 ON T2.fr01codemp = T1.fr01codemp AND
T2.fr02codigo = T1.fr02codigo
LEFT JOIN FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod =
T2.fr09cod
WHERE
(T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >=
'01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999')
AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
And re-test performance again.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/;
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
@ 2015-08-05 19:55 ` Andreas Joseph Krogh <andreas@visena.com>
2015-08-05 20:53 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 21:06 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
1 sibling, 2 replies; 13+ messages in thread
From: Andreas Joseph Krogh @ 2015-08-05 19:55 UTC (permalink / raw)
To: pgsql-performance
På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <maxim.boguk@gmail.com
<mailto:maxim.boguk@gmail.com>>: [snip] I think I know where issue is.
The PostgreSQL planner unable pass join conditions into subquery with
aggregate functions (it's well known limitation).
[snip]
I'm curious; will 9.5 help here as it has "WHERE clause pushdown in subqueries
with window functions"?
http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-where-pushdown-with-window-fun...
Are you able to try 9.5 and post the results?
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 19:55 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
@ 2015-08-05 20:53 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 21:00 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
1 sibling, 1 reply; 13+ messages in thread
From: Alexandre de Arruda Paes @ 2015-08-05 20:53 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Hi Andreas,
Same plan in 9.5, but the execution time was greater than 9.3 (maybe need
some tunning):
postgres@hw-prox01-fac:~/PG95$ /usr/PG95/bin/psql copro95 -p 5444
psql (9.5alpha1)
Type "help" for help.
copro95=# explain (analyze,buffers) SELECT
T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS
fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
T1.fr02codigo,T1.fr01codemp FROM((((FR13T T1 LEFT JOIN FR02T T2 ON
T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc >=
'01/05/2014') AND (T1.fr02codigo >= '0' and T1.fr02codigo <= '9999999999')
AND (T1.fr13dtlanc <= '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=30535.97..33949.17 rows=1 width=130) (actual
time=623.008..1029.130 rows=2 loops=1)
Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 368
Buffers: shared hit=21362
-> Nested Loop Left Join (cost=30529.83..33941.87 rows=1 width=98)
(actual time=622.761..1028.782 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=21360
-> Nested Loop Left Join (cost=30529.70..33941.71 rows=1
width=87) (actual time=622.709..1028.699 rows=2 loops=1)
Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 500202
Buffers: shared hit=21356
-> Nested Loop Left Join (cost=0.70..2087.56 rows=1
width=23) (actual time=1.021..2.630 rows=2 loops=1)
Buffers: shared hit=181
-> Index Scan using ufr13t2 on fr13t t1
(cost=0.42..2083.24 rows=1 width=19) (actual time=0.996..2.576 rows=2
loops=1)
Index Cond: ((fr01codemp = '1'::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
Filter: ((fr02codigo >= '0'::numeric) AND
(fr02codigo <= '9999999999'::numeric) AND (fr13codpr = 60732))
Rows Removed by Filter: 5621
Buffers: shared hit=175
-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=0.013..0.016 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = '1'::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=6
-> HashAggregate (cost=30529.00..30996.70 rows=31180
width=21) (actual time=286.123..457.848 rows=250102 loops=2)
Group Key: fr13t1.fr01codemp, fr13t1.fr02codigo,
fr13t1.fr13dtlanc
Buffers: shared hit=21175
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
Filter: (fr01codemp = '1'::smallint)
Buffers: shared hit=21175
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
rows=1 width=15) (actual time=0.026..0.027 rows=1 loops=2)
Index Cond: ((fr01codemp = '1'::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=4
-> HashAggregate (cost=6.14..6.50 rows=29 width=17) (actual
time=0.082..0.128 rows=184 loops=2)
Group Key: fr13t3.fr01codemp, fr13t3.fr02codigo, fr13t3.fr13dtlanc
Buffers: shared hit=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=0.011..0.033 rows=184 loops=1)
Filter: (fr01codemp = '1'::smallint)
Buffers: shared hit=2
Planning time: 2.394 ms
Execution time: 1038.785 ms
(38 rows)
copro95=#
2015-08-05 16:55 GMT-03:00 Andreas Joseph Krogh <andreas@visena.com>:
> På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <
> maxim.boguk@gmail.com>:
>
> [snip]
>
> I think I know where issue is.
> The PostgreSQL planner unable pass join conditions into subquery with
> aggregate functions (it's well known limitation).
> [snip]
>
>
> I'm curious; will 9.5 help here as it has "WHERE clause pushdown in
> subqueries with window functions"?
>
> http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-where-pushdown-with-window-fun...
>
> Are you able to try 9.5 and post the results?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 19:55 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
2015-08-05 20:53 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
@ 2015-08-05 21:00 ` Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 0 replies; 13+ messages in thread
From: Andreas Joseph Krogh @ 2015-08-05 21:00 UTC (permalink / raw)
To: pgsql-performance
På onsdag 05. august 2015 kl. 22:53:25, skrev Alexandre de Arruda Paes <
adaldeia@gmail.com <mailto:adaldeia@gmail.com>>:
Hi Andreas,
Same plan in 9.5, but the execution time was greater than 9.3 (maybe need some
tunning):
Thanks for sharing.
Maybe some @hackers will chime in and comment.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 19:55 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
@ 2015-08-05 21:06 ` David Rowley <david.rowley@2ndquadrant.com>
1 sibling, 0 replies; 13+ messages in thread
From: David Rowley @ 2015-08-05 21:06 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-performance
On 6 August 2015 at 07:55, Andreas Joseph Krogh <andreas@visena.com> wrote:
> På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <
> maxim.boguk@gmail.com>:
>
> [snip]
>
> I think I know where issue is.
> The PostgreSQL planner unable pass join conditions into subquery with
> aggregate functions (it's well known limitation).
> [snip]
>
>
> I'm curious; will 9.5 help here as it has "WHERE clause pushdown in
> subqueries with window functions"?
>
> http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-where-pushdown-with-window-fun...
>
>
>
I've not looked at the query in any detail, but that particular patch won't
help as it only allows pushdown of predicate into subqueries with window
functions where the predicate is part of all of the subquery's PARTITION BY
clauses.
The query in question has no window clauses, so qual pushdown is not
disabled for that reason.
Regards
David Rowley
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/;
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
@ 2015-08-05 22:07 ` David Rowley <david.rowley@2ndquadrant.com>
2015-08-05 23:58 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-06 01:09 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: David Rowley @ 2015-08-05 22:07 UTC (permalink / raw)
To: Maxim Boguk <maxim.boguk@gmail.com>; +Cc: Alexandre de Arruda Paes <adaldeia@gmail.com>; pgsql-performance
On 6 August 2015 at 06:25, Maxim Boguk <maxim.boguk@gmail.com> wrote:
>
>
> On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <
> adaldeia@gmail.com> wrote:
>
>> Hi,
>>
>> First, sorry to compare Post with other database system, but I know
>> nothing about Oracle...
>>
>> This customer have an application made with a framework thats generates
>> the SQL statements (so, We can't make any query optimizations) .
>>
>> We did the following tests:
>>
>> 1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
>> disk,Core i5)
>> 2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores,
>> SAS disks)
>>
>>
>> I think I know where issue is.
> The PostgreSQL planner unable pass join conditions into subquery with
> aggregate functions (it's well known limitation).
>
>
I think this statement is quite misleading. Let's look at an example:
create table t1 (a int not null, v int not null);
create table t2 (a int not null);
insert into t1 select s.i,10 from generate_series(1,1000)
s(i),generate_series(1,1000);
insert into t2 select generate_series(1,1000);
create index on t1 (a);
explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 group by a) s
inner join t2 on t2.a = s.a where t2.a = 1;
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=0.42..59.76 rows=1 width=12)
-> GroupAggregate (cost=0.42..42.24 rows=1 width=8)
Group Key: t1.a
-> Index Scan using t1_a_idx on t1 (cost=0.42..37.38 rows=969
width=8)
Index Cond: (a = 1)
-> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
Filter: (a = 1)
(7 rows)
As you can see, the predicate is pushes down just fine into a subquery with
aggregates.
The likely reason that PostgreSQL Is not behaving the same as SQL Server
and Oracle is because the predicate pushdowns are limited to equality
operators only as internally these are all represented by a series of
"equivalence classes" which in this case say that 1 = t2.a = t1.a,
therefore it's possible to apply t1.a = 1 at the lowest level.
These equivalence classes don't currently handle non-equality operators.
Here's an example:
explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 group by a) s
inner join t2 on t2.a = s.a where t2.a <= 1;
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=19442.51..19466.27 rows=1 width=12)
Hash Cond: (t1.a = t2.a)
-> HashAggregate (cost=19425.00..19435.00 rows=1000 width=8)
Group Key: t1.a
-> Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=8)
-> Hash (cost=17.50..17.50 rows=1 width=4)
-> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
Filter: (a <= 1)
(8 rows)
Notice the seq scan on t1 instead of the index scan on t1_a_idx.
A way around this is to manually push the predicate down into the subquery:
explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a <=
1 group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.42..21.98 rows=1 width=12)
Join Filter: (t1.a = t2.a)
-> GroupAggregate (cost=0.42..4.46 rows=1 width=8)
Group Key: t1.a
-> Index Scan using t1_a_idx on t1 (cost=0.42..4.44 rows=1
width=8)
Index Cond: (a <= 1)
-> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
Filter: (a <= 1)
(8 rows)
The query in question is likely performing badly because of this:
-> Seq Scan on fr13t1 (cost=0.00..25072.50
rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
Filter: (fr01codemp = '1'::smallint)
Buffers: shared hit=21175
Just how selective is fr01codemp = '1'::smallint ? Is there an index on
that column ?
Regards
David Rowley
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/;
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 22:07 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
@ 2015-08-05 23:58 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alexandre de Arruda Paes @ 2015-08-05 23:58 UTC (permalink / raw)
To: pgsql-performance
>
>
> The query in question is likely performing badly because of this:
>
> -> Seq Scan on fr13t1 (cost=0.00..25072.50
> rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
> Filter: (fr01codemp = '1'::smallint)
> Buffers: shared hit=21175
>
> Just how selective is fr01codemp = '1'::smallint ? Is there an index on
> that column ?
>
>
Hi David,
In this case, fr13t1 has only value '1' in all fr01codemp:
copro95=# select fr01codemp,count(*) from fr13t1 group by fr01codemp;
fr01codemp | count
------------+--------
1 | 311800
(1 row)
Table "public.fr13t1"
Column | Type | Modifiers
------------+-----------------------------+-----------
fr01codemp | smallint | not null
fr02codigo | numeric(10,0) | not null
fr13dtlanc | date | not null
fr13sequen | smallint | not null
(...)
Indexes:
"fr13t1_pkey" PRIMARY KEY, btree (fr01codemp, fr02codigo, fr13dtlanc,
fr13sequen)
"ifr13t1" btree (fr01codemp, fr07cod)
"ifr13t12" btree (co18codord)
"ifr13t14" btree (fr01codemp, fr52mot)
(...)
If planner needs to scan all table, can indexscan/indexonlyscan can take
any advantage ?
Besta regards,
Alexandre
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 22:07 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
@ 2015-08-06 01:09 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-06 10:05 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
1 sibling, 1 reply; 13+ messages in thread
From: Alexandre de Arruda Paes @ 2015-08-06 01:09 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: Maxim Boguk <maxim.boguk@gmail.com>; pgsql-performance
>
>
> Notice the seq scan on t1 instead of the index scan on t1_a_idx.
>
> A way around this is to manually push the predicate down into the subquery:
>
> explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a
> <= 1 group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;
> QUERY PLAN
>
> -------------------------------------------------------------------------------
> Nested Loop (cost=0.42..21.98 rows=1 width=12)
> Join Filter: (t1.a = t2.a)
> -> GroupAggregate (cost=0.42..4.46 rows=1 width=8)
> Group Key: t1.a
> -> Index Scan using t1_a_idx on t1 (cost=0.42..4.44 rows=1
> width=8)
> Index Cond: (a <= 1)
> -> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
> Filter: (a <= 1)
> (8 rows)
>
>
>
Hi David,
You are right. If the subquery includes the same filters of the main select
(of the existing fields, sure), the times down to the floor (50 ms in the
first execution and *18* ms by cache. Superb! ):
(...) (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 *WHERE (fr01codemp = '1' and fr13dtlanc >= '01/05/2014') AND
(fr02codigo >= '0' and fr02codigo <= '9999999999') AND (fr13dtlanc <=
'31/05/2014') *GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
(...)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=5770.32..7894.70 rows=1 width=130) (actual
time=13.715..18.366 rows=2 loops=1)
Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 368
Buffers: shared hit=5920
-> Nested Loop Left Join (cost=5764.18..7887.47 rows=1 width=98)
(actual time=13.529..18.108 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=5918
-> Nested Loop Left Join (cost=5764.04..7887.30 rows=1 width=87)
(actual time=13.519..18.094 rows=2 loops=1)
Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 11144
Buffers: shared hit=5914
-> Nested Loop Left Join (cost=0.70..2098.42 rows=1
width=23) (actual time=0.796..2.071 rows=2 loops=1)
Buffers: shared hit=181
-> Index Scan using ufr13t2 on fr13t t1
(cost=0.42..2094.11 rows=1 width=19) (actual time=0.787..2.054 rows=2
loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
Filter: ((fr02codigo >= 0::numeric) AND
(fr02codigo <= 9999999999::numeric) AND (fr13codpr = 60732))
Rows Removed by Filter: 5621
Buffers: shared hit=175
-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=6
-> HashAggregate (cost=5763.34..5770.15 rows=681 width=21)
(actual time=5.576..6.787 rows=5573 loops=2)
Buffers: shared hit=5733
-> Index Scan using ufr13t15 on fr13t1
(cost=0.42..5644.31 rows=6802 width=21) (actual time=0.020..3.371
rows=7053 loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date)
AND (fr02codigo >= 0::numeric) AND (fr02codigo <= 9999999999::numeric))
Buffers: shared hit=5733
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=2)
Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=4
-> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
time=0.056..0.086 rows=184 loops=2)
Buffers: shared hit=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
(actual time=0.003..0.027 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared hit=2
Total runtime: 18.528 ms
(35 rows)
Tomorrow I will try to do the same with the other slow query, reporting
here.
Best regards,
Alexandre
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 22:07 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
2015-08-06 01:09 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
@ 2015-08-06 10:05 ` Andreas Joseph Krogh <andreas@visena.com>
2015-08-10 03:16 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 13+ messages in thread
From: Andreas Joseph Krogh @ 2015-08-06 10:05 UTC (permalink / raw)
To: pgsql-performance
På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes <
adaldeia@gmail.com <mailto:adaldeia@gmail.com>>:
Notice the seq scan on t1 instead of the index scan on t1_a_idx.
A way around this is to manually push the predicate down into the subquery:
explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a <= 1
group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.42..21.98 rows=1 width=12)
Join Filter: (t1.a = t2.a)
-> GroupAggregate (cost=0.42..4.46 rows=1 width=8)
Group Key: t1.a
-> Index Scan using t1_a_idx on t1 (cost=0.42..4.44 rows=1 width=8)
Index Cond: (a <= 1)
-> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
Filter: (a <= 1)
(8 rows)
Hi David,
You are right. If the subquery includes the same filters of the main select
(of the existing fields, sure), the times down to the floor (50 ms in the first
execution and *18* ms by cache. Superb! ):
(...) (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 WHERE (fr01codemp = '1' and fr13dtlanc >= '01/05/2014') AND
(fr02codigo >= '0' and fr02codigo <= '9999999999') AND (fr13dtlanc <=
'31/05/2014')GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON T4.fr01codemp
= T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND T4.fr13dtlanc =
T1.fr13dtlanc)
(...)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=5770.32..7894.70 rows=1 width=130) (actual
time=13.715..18.366 rows=2 loops=1)
Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo =
t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 368
Buffers: shared hit=5920
-> Nested Loop Left Join (cost=5764.18..7887.47 rows=1 width=98) (actual
time=13.529..18.108 rows=2 loops=1)
Join Filter: (t3.fr01codemp = t1.fr01codemp)
Buffers: shared hit=5918
-> Nested Loop Left Join (cost=5764.04..7887.30 rows=1 width=87)
(actual time=13.519..18.094 rows=2 loops=1)
Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
Rows Removed by Join Filter: 11144
Buffers: shared hit=5914
-> Nested Loop Left Join (cost=0.70..2098.42 rows=1 width=23)
(actual time=0.796..2.071 rows=2 loops=1)
Buffers: shared hit=181
-> Index Scan using ufr13t2 on fr13t t1
(cost=0.42..2094.11 rows=1 width=19) (actual time=0.787..2.054 rows=2 loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
Filter: ((fr02codigo >= 0::numeric) AND (fr02codigo
<= 9999999999::numeric) AND (fr13codpr = 60732))
Rows Removed by Filter: 5621
Buffers: shared hit=175
-> Index Scan using fr02t_pkey on fr02t t2
(cost=0.28..4.30 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2)
Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
Buffers: shared hit=6
-> HashAggregate (cost=5763.34..5770.15 rows=681 width=21)
(actual time=5.576..6.787 rows=5573 loops=2)
Buffers: shared hit=5733
-> Index Scan using ufr13t15 on fr13t1
(cost=0.42..5644.31 rows=6802 width=21) (actual time=0.020..3.371 rows=7053
loops=1)
Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date) AND
(fr02codigo >= 0::numeric) AND (fr02codigo <= 9999999999::numeric))
Buffers: shared hit=5733
-> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16 rows=1
width=15) (actual time=0.005..0.005 rows=1 loops=2)
Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
Buffers: shared hit=4
-> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
time=0.056..0.086 rows=184 loops=2)
Buffers: shared hit=2
-> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17) (actual
time=0.003..0.027 rows=184 loops=1)
Filter: (fr01codemp = 1::smallint)
Buffers: shared hit=2
Total runtime: 18.528 ms
(35 rows)
Tomorrow I will try to do the same with the other slow query, reporting here.
It will be interesting to see how Oracle and SQL-Server perform with the
re-written query too.
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow HashAggregate/cache access
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Re: Slow HashAggregate/cache access Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 22:07 ` Re: Slow HashAggregate/cache access David Rowley <david.rowley@2ndquadrant.com>
2015-08-06 01:09 ` Re: Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-06 10:05 ` Re: Slow HashAggregate/cache access Andreas Joseph Krogh <andreas@visena.com>
@ 2015-08-10 03:16 ` David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 0 replies; 13+ messages in thread
From: David Rowley @ 2015-08-10 03:16 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-performance
On 6 August 2015 at 22:05, Andreas Joseph Krogh <andreas@visena.com> wrote:
> På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes <
> adaldeia@gmail.com>:
>
>
>> Notice the seq scan on t1 instead of the index scan on t1_a_idx.
>>
>> A way around this is to manually push the predicate down into the
>> subquery:
>>
>> explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a
>> <= 1 group by a) s inner join t2 on t2.a = s.a where t2.a <= 1;
>> QUERY PLAN
>>
>> -------------------------------------------------------------------------------
>> Nested Loop (cost=0.42..21.98 rows=1 width=12)
>> Join Filter: (t1.a = t2.a)
>> -> GroupAggregate (cost=0.42..4.46 rows=1 width=8)
>> Group Key: t1.a
>> -> Index Scan using t1_a_idx on t1 (cost=0.42..4.44 rows=1
>> width=8)
>> Index Cond: (a <= 1)
>> -> Seq Scan on t2 (cost=0.00..17.50 rows=1 width=4)
>> Filter: (a <= 1)
>> (8 rows)
>>
>>
>>
>
> Hi David,
>
> You are right. If the subquery includes the same filters of the main
> select (of the existing fields, sure), the times down to the floor (50 ms
> in the first execution and *18* ms by cache. Superb! ):
>
> (...) (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
> fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
> NUMERIC(18,10))) AS fr13VrTot
> FROM FR13T1 *WHERE (fr01codemp = '1' and fr13dtlanc >= '01/05/2014') AND
> (fr02codigo >= '0' and fr02codigo <= '9999999999') AND (fr13dtlanc <=
> '31/05/2014') *GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
> T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
> T4.fr13dtlanc = T1.fr13dtlanc)
> (...)
>
>
> QUERY PLAN
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop Left Join (cost=5770.32..7894.70 rows=1 width=130) (actual
> time=13.715..18.366 rows=2 loops=1)
> Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND
> (fr13t3.fr02codigo = t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
> Rows Removed by Join Filter: 368
> Buffers: shared hit=5920
> -> Nested Loop Left Join (cost=5764.18..7887.47 rows=1 width=98)
> (actual time=13.529..18.108 rows=2 loops=1)
> Join Filter: (t3.fr01codemp = t1.fr01codemp)
> Buffers: shared hit=5918
> -> Nested Loop Left Join (cost=5764.04..7887.30 rows=1
> width=87) (actual time=13.519..18.094 rows=2 loops=1)
> Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
> (fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
> Rows Removed by Join Filter: 11144
> Buffers: shared hit=5914
> -> Nested Loop Left Join (cost=0.70..2098.42 rows=1
> width=23) (actual time=0.796..2.071 rows=2 loops=1)
> Buffers: shared hit=181
> -> Index Scan using ufr13t2 on fr13t t1
> (cost=0.42..2094.11 rows=1 width=19) (actual time=0.787..2.054 rows=2
> loops=1)
> Index Cond: ((fr01codemp = 1::smallint) AND
> (fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date))
> Filter: ((fr02codigo >= 0::numeric) AND
> (fr02codigo <= 9999999999::numeric) AND (fr13codpr = 60732))
> Rows Removed by Filter: 5621
> Buffers: shared hit=175
> -> Index Scan using fr02t_pkey on fr02t t2
> (cost=0.28..4.30 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2)
> Index Cond: ((fr01codemp = t1.fr01codemp) AND
> (fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
> Buffers: shared hit=6
> -> HashAggregate (cost=5763.34..5770.15 rows=681
> width=21) (actual time=5.576..6.787 rows=5573 loops=2)
> Buffers: shared hit=5733
> -> Index Scan using ufr13t15 on fr13t1
> (cost=0.42..5644.31 rows=6802 width=21) (actual time=0.020..3.371
> rows=7053 loops=1)
> Index Cond: ((fr01codemp = 1::smallint) AND
> (fr13dtlanc >= '2014-05-01'::date) AND (fr13dtlanc <= '2014-05-31'::date)
> AND (fr02codigo >= 0::numeric) AND (fr02codigo <= 9999999999::numeric))
> Buffers: shared hit=5733
> -> Index Scan using fr09t_pkey on fr09t t3 (cost=0.14..0.16
> rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=2)
> Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
> t2.fr09cod))
> Buffers: shared hit=4
> -> HashAggregate (cost=6.14..6.43 rows=29 width=17) (actual
> time=0.056..0.086 rows=184 loops=2)
> Buffers: shared hit=2
> -> Seq Scan on fr13t3 (cost=0.00..4.30 rows=184 width=17)
> (actual time=0.003..0.027 rows=184 loops=1)
> Filter: (fr01codemp = 1::smallint)
> Buffers: shared hit=2
> Total runtime: 18.528 ms
> (35 rows)
>
>
> Tomorrow I will try to do the same with the other slow query, reporting
> here.
>
>
> It will be interesting to see how Oracle and SQL-Server perform with the
> re-written query too.
> Thanks.
>
>
Glad that's looking better for you.
I'd guess that they're likely already pushing down those predicates into
the subquery going by the execution times that you posted.
I can't imagine Oracle can perform a seq scan / table scan that much faster
than Postgres
Interested to hear the results of your tests though.
Regards
David Rowley
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/;
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2015-08-10 03:16 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-05 01:41 Slow HashAggregate/cache access Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 17:24 ` Kevin Grittner <kgrittn@ymail.com>
2015-08-05 19:29 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 18:25 ` Maxim Boguk <maxim.boguk@gmail.com>
2015-08-05 19:55 ` Andreas Joseph Krogh <andreas@visena.com>
2015-08-05 20:53 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-05 21:00 ` Andreas Joseph Krogh <andreas@visena.com>
2015-08-05 21:06 ` David Rowley <david.rowley@2ndquadrant.com>
2015-08-05 22:07 ` David Rowley <david.rowley@2ndquadrant.com>
2015-08-05 23:58 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-06 01:09 ` Alexandre de Arruda Paes <adaldeia@gmail.com>
2015-08-06 10:05 ` Andreas Joseph Krogh <andreas@visena.com>
2015-08-10 03:16 ` David Rowley <david.rowley@2ndquadrant.com>
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