[PERFORM] Effect of the WindowAgg on the Nested Loop

2013-01-22 Thread Виктор Егоров
Greetings. I've been playing with a small query that I've been asked to optimize and noticed a strange (for me) effect. Query uses this table: Table "clc06_tiles" Column | Type | Modifiers +---+--

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread Tom Lane
Andrew Dunstan writes: > On 01/22/2013 09:21 AM, rudi wrote: >> The query is pretty simple and standard, the behaviour (and the plan) >> is totally different when it comes to a partitioned table. >> >> Partioned table query => explain analyze SELECT "sb_logs".* FROM >> "sb_logs" WHERE (device

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread Igor Neyman
In PG 9.2 I’m getting “Index Only Scan Backward” for every partition in the first part of execution plan, when looking for MAX in partitioned table on a similar query: "-> Index Only Scan Backward using pk_cycle_200610 on gp_cycle_200610 gp_cycle (cost=0.00..8.34 rows=

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread rudi
On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan wrote: > The query is pretty simple and standard, the behaviour (and the plan) is > totally different when it comes to a partitioned table. > >> >> Partioned table query => explain analyze SELECT "sb_logs".* FROM >> "sb_logs" WHERE (device_id = 90

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread Andrew Dunstan
On 01/22/2013 09:21 AM, rudi wrote: On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure > wrote: let's see the query -- it's probably written in such a way so as to not be able to be optimized through CE. The query is pretty simple and standard, the behaviour (a

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread rudi
On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure wrote: > let's see the query -- it's probably written in such a way so as to > not be able to be optimized through CE. > > The query is pretty simple and standard, the behaviour (and the plan) is totally different when it comes to a partitioned tabl

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread Merlin Moncure
On Tue, Jan 22, 2013 at 7:34 AM, rudi wrote: > Every query has been carefully optimized, child tables are indexed. The > table(s) has a UNIQUE index on ("date_taken", "device_id") and "date_taken" > is the partitioning column (one partition per year). > There are few well known access path to this

Re: [PERFORM] High CPU usage after partitioning

2013-01-22 Thread rudi
On Tue, Jan 22, 2013 at 1:41 AM, Andrew Dunstan wrote: > > On 01/21/2013 10:05 AM, rudi wrote: > >> Hello, >> >> I'm running postgresl 9.0. After partitioning a big table, CPU usage >> raised from average 5-10% to average 70-80%. >> >> - the table contains about 20.000.000 rows >> - partitions ar