Re: [PERFORM] Performance issue with NestedLoop query
On Tue, Aug 4, 2015 at 8:40 PM, Ram N yrami...@gmail.com 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
Alexandre de Arruda Paes adald...@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
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 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/ 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.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
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 kgri...@ymail.com: Alexandre de Arruda Paes adald...@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
Re: [PERFORM] Slow HashAggregate/cache access
På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim Boguk maxim.bo...@gmail.com 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 mailto:andr...@visena.com www.visena.com https://www.visena.com https://www.visena.com
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,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 andr...@visena.com: På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim
Re: [PERFORM] Slow HashAggregate/cache access
På onsdag 05. august 2015 kl. 22:53:25, skrev Alexandre de Arruda Paes adald...@gmail.com mailto: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 mailto:andr...@visena.com www.visena.com https://www.visena.com https://www.visena.com
Re: [PERFORM] Slow HashAggregate/cache access
On 6 August 2015 at 07:55, Andreas Joseph Krogh andr...@visena.com 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/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [PERFORM] Slow HashAggregate/cache access
On 6 August 2015 at 06:25, Maxim Boguk maxim.bo...@gmail.com 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/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
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; 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] 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) 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
