date:20150805

Re: [PERFORM] Performance issue with NestedLoop query

2015-08-05 Thread Qingqing Zhou

On Tue, Aug 4, 2015 at 8:40 PM, Ram N  wrote:
>
> Thanks much for responding guys. I have tried both, building multi column
> indexes and GIST, with no improvement. I have reduced the window from 180
> days to 30 days and below are the numbers
>
> Composite index -  takes 30 secs
>
> With Btree indexing  - takes 9 secs
>
> With GIST - takes >30 secs with kind of materialize plan in explain
>
> Any other ideas I can do for window based joins.
>

>From this query:

select sum(a), count(id), a.ts, st from table1 a, table2 b where a.ts
> b.start_date and a.ts < b.end_date and a.ts > '2015-01-01
20:50:44.00 +00:00:00' and a.ts < '2015-07-01 19:50:44.00
+00:00:00' group by a.ts, st order by a.ts

We can actually derive that b.start_date > '2015-07-01 19:50:44.00
+00:00:00' and b.end_date < '2015-01-01 20:50:44.00 +00:00:00'. If
we add these two predicates to the original query, does it help?

Thanks,
Qingqing


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Kevin Grittner

Alexandre de Arruda Paes  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

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Maxim Boguk

On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <
adald...@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 FROMFR13T 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 <= '99')
> 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 <= '99')
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/ 

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.bo...@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."

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Alexandre de Arruda Paes

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 :

> Alexandre de Arruda Paes  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
>

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Andreas Joseph Krogh

På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk mailto:maxim.bo...@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-function/

 
Are you able to try 9.5 and post the results?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Alexandre de Arruda Paes

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 FROMFR13T 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 <= '99')
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 <= '99'::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 :

> På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim B

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Andreas Joseph Krogh

På onsdag 05. august 2015 kl. 22:53:25, skrev Alexandre de Arruda Paes <
adald...@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
andr...@visena.com 
www.visena.com

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread David Rowley

On 6 August 2015 at 07:55, Andreas Joseph Krogh  wrote:

> På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk <
> maxim.bo...@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-function/
>
>
>
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/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread David Rowley

On 6 August 2015 at 06:25, Maxim Boguk  wrote:

>
>
> On Wed, Aug 5, 2015 at 11:41 AM, Alexandre de Arruda Paes <
> adald...@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=100 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/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Alexandre de Arruda Paes

>
>
> 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

Re: [PERFORM] Slow HashAggregate/cache access

2015-08-05 Thread Alexandre de Arruda Paes

>
>
> 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 <= '99') 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 <= 99::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 <= 99::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

Re: [PERFORM] Performance issue with NestedLoop query

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

Re: [PERFORM] Slow HashAggregate/cache access

11 matches

Site Navigation

Mail list logo

Footer information