Re: [PERFORM] Slow HashAggregate/cache access

On 6 August 2015 at 22:05, Andreas Joseph Krogh wrote: > På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes < > adald...@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

Re: [PERFORM] Slow HashAggregate/cache access

På torsdag 06. august 2015 kl. 03:09:55, skrev Alexandre de Arruda Paes < adald...@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 fr

Re: [PERFORM] Slow HashAggregate/cache access

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

Re: [PERFORM] Slow HashAggregate/cache access

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

Re: [PERFORM] Slow HashAggregate/cache access

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 w

Re: [PERFORM] Slow HashAggregate/cache access

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 kn

Re: [PERFORM] Slow HashAggregate/cache access

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

Re: [PERFORM] Slow HashAggregate/cache access

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

Re: [PERFORM] Slow HashAggregate/cache access

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

Re: [PERFORM] Slow HashAggregate/cache access

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 fr

Re: [PERFORM] Slow HashAggregate/cache access

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 ca

Re: [PERFORM] Slow HashAggregate/cache access

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

[PERFORM] Slow HashAggregate/cache access

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