pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHigh CPU usage after partitioning
11+ messages / 6 participants
[nested] [flat]
* High CPU usage after partitioning
@ 2013-01-21 15:05 rudi <rudolone@gmail.com>
2013-01-21 17:13 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
0 siblings, 2 replies; 11+ messages in thread
From: rudi @ 2013-01-21 15:05 UTC (permalink / raw)
To: pgsql-performance
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 are selected using a trigger, based on an indexed field, a
date (IF date_taken >= x AND date_taken < y)
- I created 5 partitions, the 2012 one now contains most of the rows. The
2013 partition is the "live" partition, mostly insert, a few select based
on the above indexed field. The 2013, 2014, 2015 partitions are empty
- constraint execution is on.
I have 2 weeks CPU usage reports and the pattern definately changed after I
made the partitions. Any idea?
thanks,
--
rd
This is the way the world ends.
Not with a bang, but a whimper.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
@ 2013-01-21 17:13 ` Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Merlin Moncure @ 2013-01-21 17:13 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 21, 2013 at 9:05 AM, rudi <rudolone@gmail.com> 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 are selected using a trigger, based on an indexed field, a date
> (IF date_taken >= x AND date_taken < y)
> - I created 5 partitions, the 2012 one now contains most of the rows. The
> 2013 partition is the "live" partition, mostly insert, a few select based on
> the above indexed field. The 2013, 2014, 2015 partitions are empty
> - constraint execution is on.
>
> I have 2 weeks CPU usage reports and the pattern definately changed after I
> made the partitions. Any idea?
First thing that jumps to mind is you have some seq-scan heavy plans
that were not seq-scan before. Could be due to query fooling CE
mechanism or some other CE (probably fixable issue). To diagnose we
need to see some explain analyze plans of queries that are using
higher than expected cpu usage.
Second possible cause is trigger overhead from inserts. Not likely to
cause so much of a jump, but if this is the issue suggested
optimization path is to insert directly to the partition.
merlin
--
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] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
@ 2013-01-22 00:41 ` Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Andrew Dunstan @ 2013-01-22 00:41 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; +Cc: pgsql-performance
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 are selected using a trigger, based on an indexed field,
> a date (IF date_taken >= x AND date_taken < y)
> - I created 5 partitions, the 2012 one now contains most of the rows.
> The 2013 partition is the "live" partition, mostly insert, a few
> select based on the above indexed field. The 2013, 2014, 2015
> partitions are empty
> - constraint execution is on.
> I have 2 weeks CPU usage reports and the pattern definately changed
> after I made the partitions. Any idea?
>
>
Well, the first question that comes to my mind is whether it's the
inserts that are causing the load or the reads. If it's the inserts then
you should show us the whole trigger. Does it by any chance use 'execute'?
cheers
andrew
--
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] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
@ 2013-01-22 13:34 ` rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: rudi @ 2013-01-22 13:34 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-performance
On Tue, Jan 22, 2013 at 1:41 AM, Andrew Dunstan <andrew@dunslane.net> 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 are selected using a trigger, based on an indexed field, a
>> date (IF date_taken >= x AND date_taken < y)
>> - I created 5 partitions, the 2012 one now contains most of the rows. The
>> 2013 partition is the "live" partition, mostly insert, a few select based
>> on the above indexed field. The 2013, 2014, 2015 partitions are empty
>> - constraint execution is on.
>> I have 2 weeks CPU usage reports and the pattern definately changed after
>> I made the partitions. Any idea?
>>
>>
>>
> Well, the first question that comes to my mind is whether it's the inserts
> that are causing the load or the reads. If it's the inserts then you should
> show us the whole trigger. Does it by any chance use 'execute'?
I think I found the culprit. The insert trigger doesn't seem to be an
issue. It is a trivial IF-ELSE and inserts seems fast.
IF (NEW.date_taken < DATE '2013-01-01') THEN
INSERT INTO sb_logs_2012 VALUES (NEW.*);
ELSIF (NEW.date_taken >= DATE '2013-01-01' AND NEW.date_taken < DATE
'2014-01-01') THEN
INSERT INTO sb_logs_2013 VALUES (NEW.*);
[...]
END IF;
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 table: INSERTs (40-50.000 each
day), SELECTs on a specific device_id AND on a specific day.
BUT, I discovered an access path used by a process every few secs. to get
the last log for a given device, and this query became really slow after
partitioning:
Result (cost=341156.04..341182.90 rows=4 width=86) (actual
time=1132.326..1132.329 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=341156.03..341156.04 rows=1 width=8) (actual
time=1132.295..1132.296 rows=1 loops=1)
-> Append (cost=0.00..341112.60 rows=17371 width=8) (actual
time=45.600..1110.057 rows=19016 loops=1)
-> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=8)
(actual time=0.000..0.000 rows=0 loops=1)
Filter: (device_id = 901)
-> Index Scan using
sb_logs_2012_on_date_taken_and_device_id on sb_logs_2012 sb_logs
(cost=0.00..319430.51 rows=16003 width=8) (actual time=45.599..1060.143
rows=17817 loops=1)
Index Cond: (device_id = 901)
-> Index Scan using
sb_logs_2013_on_date_taken_and_device_id on sb_logs_2013 sb_logs
(cost=0.00..21663.39 rows=1363 width=8) (actual time=0.022..47.661
rows=1199 loops=1)
Index Cond: (device_id = 901)
-> Bitmap Heap Scan on sb_logs_2014 sb_logs
(cost=10.25..18.71 rows=4 width=8) (actual time=0.011..0.011 rows=0
loops=1)
Recheck Cond: (device_id = 901)
-> Bitmap Index Scan on
sb_logs_2014_on_date_taken_and_device_id (cost=0.00..10.25 rows=4 width=0)
(actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (device_id = 901)
-> Append (cost=0.00..26.86 rows=4 width=86) (actual
time=1132.325..1132.328 rows=1 loops=1)
-> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=90) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: ((device_id = 901) AND (date_taken = $0))
-> Index Scan using sb_logs_2012_on_date_taken_and_device_id on
sb_logs_2012 sb_logs (cost=0.00..10.20 rows=1 width=90) (actual
time=1132.314..1132.314 rows=0 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
-> Index Scan using sb_logs_2013_on_date_taken_and_device_id on
sb_logs_2013 sb_logs (cost=0.00..8.39 rows=1 width=91) (actual
time=0.007..0.008 rows=1 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
-> Index Scan using sb_logs_2014_on_date_taken_and_device_id on
sb_logs_2014 sb_logs (cost=0.00..8.27 rows=1 width=72) (actual
time=0.002..0.002 rows=0 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
Total runtime: 1132.436 ms
I must find a better way to get that information, but I wonder if there
could be a better plan. The same query over a table with the same structure
but not partitioned gives far better plan:
Index Scan using index_iv_logs_on_date_taken_and_device_id on iv_logs
(cost=12.35..21.88 rows=1 width=157) (actual time=0.065..0.066 rows=1
loops=1)
Index Cond: ((date_taken = $1) AND (device_id = 1475))
InitPlan 2 (returns $1)
-> Result (cost=12.34..12.35 rows=1 width=0) (actual
time=0.059..0.059 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..12.34 rows=1 width=8) (actual
time=0.055..0.056 rows=1 loops=1)
-> Index Scan Backward using
index_iv_logs_on_date_taken_and_device_id on iv_logs (cost=0.00..261052.53
rows=21154 width=8) (actual time=0.055..0.055 rows=1 loops=1)
Index Cond: ((date_taken IS NOT NULL) AND
(device_id = 1475))
Total runtime: 0.110 ms
--
rd
This is the way the world ends.
Not with a bang, but a whimper.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
@ 2013-01-22 14:04 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Merlin Moncure @ 2013-01-22 14:04 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-performance
On Tue, Jan 22, 2013 at 7:34 AM, rudi <rudolone@gmail.com> 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 table: INSERTs (40-50.000 each
> day), SELECTs on a specific device_id AND on a specific day.
>
> BUT, I discovered an access path used by a process every few secs. to get
> the last log for a given device, and this query became really slow after
> partitioning:
>
> Result (cost=341156.04..341182.90 rows=4 width=86) (actual
> time=1132.326..1132.329 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Aggregate (cost=341156.03..341156.04 rows=1 width=8) (actual
> time=1132.295..1132.296 rows=1 loops=1)
> -> Append (cost=0.00..341112.60 rows=17371 width=8) (actual
> time=45.600..1110.057 rows=19016 loops=1)
> -> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=8)
> (actual time=0.000..0.000 rows=0 loops=1)
> Filter: (device_id = 901)
> -> Index Scan using
> sb_logs_2012_on_date_taken_and_device_id on sb_logs_2012 sb_logs
> (cost=0.00..319430.51 rows=16003 width=8) (actual time=45.599..1060.143
> rows=17817 loops=1)
> Index Cond: (device_id = 901)
> -> Index Scan using
> sb_logs_2013_on_date_taken_and_device_id on sb_logs_2013 sb_logs
> (cost=0.00..21663.39 rows=1363 width=8) (actual time=0.022..47.661 rows=1199
> loops=1)
> Index Cond: (device_id = 901)
> -> Bitmap Heap Scan on sb_logs_2014 sb_logs
> (cost=10.25..18.71 rows=4 width=8) (actual time=0.011..0.011 rows=0 loops=1)
> Recheck Cond: (device_id = 901)
> -> Bitmap Index Scan on
> sb_logs_2014_on_date_taken_and_device_id (cost=0.00..10.25 rows=4 width=0)
> (actual time=0.008..0.008 rows=0 loops=1)
> Index Cond: (device_id = 901)
> -> Append (cost=0.00..26.86 rows=4 width=86) (actual
> time=1132.325..1132.328 rows=1 loops=1)
> -> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=90) (actual
> time=0.002..0.002 rows=0 loops=1)
> Filter: ((device_id = 901) AND (date_taken = $0))
> -> Index Scan using sb_logs_2012_on_date_taken_and_device_id on
> sb_logs_2012 sb_logs (cost=0.00..10.20 rows=1 width=90) (actual
> time=1132.314..1132.314 rows=0 loops=1)
> Index Cond: ((date_taken = $0) AND (device_id = 901))
> -> Index Scan using sb_logs_2013_on_date_taken_and_device_id on
> sb_logs_2013 sb_logs (cost=0.00..8.39 rows=1 width=91) (actual
> time=0.007..0.008 rows=1 loops=1)
> Index Cond: ((date_taken = $0) AND (device_id = 901))
> -> Index Scan using sb_logs_2014_on_date_taken_and_device_id on
> sb_logs_2014 sb_logs (cost=0.00..8.27 rows=1 width=72) (actual
> time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((date_taken = $0) AND (device_id = 901))
> Total runtime: 1132.436 ms
>
> I must find a better way to get that information, but I wonder if there
> could be a better plan. The same query over a table with the same structure
> but not partitioned gives far better plan:
>
> Index Scan using index_iv_logs_on_date_taken_and_device_id on iv_logs
> (cost=12.35..21.88 rows=1 width=157) (actual time=0.065..0.066 rows=1
> loops=1)
> Index Cond: ((date_taken = $1) AND (device_id = 1475))
> InitPlan 2 (returns $1)
> -> Result (cost=12.34..12.35 rows=1 width=0) (actual time=0.059..0.059
> rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..12.34 rows=1 width=8) (actual
> time=0.055..0.056 rows=1 loops=1)
> -> Index Scan Backward using
> index_iv_logs_on_date_taken_and_device_id on iv_logs (cost=0.00..261052.53
> rows=21154 width=8) (actual time=0.055..0.055 rows=1 loops=1)
> Index Cond: ((date_taken IS NOT NULL) AND (device_id
> = 1475))
> Total runtime: 0.110 ms
let's see the query -- it's probably written in such a way so as to
not be able to be optimized through CE.
merlin
--
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] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
@ 2013-01-22 14:21 ` rudi <rudolone@gmail.com>
2013-01-22 14:46 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 11+ messages in thread
From: rudi @ 2013-01-22 14:21 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure <mmoncure@gmail.com> 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 table.
Partioned table query => explain analyze SELECT "sb_logs".* FROM "sb_logs"
WHERE (device_id = 901 AND date_taken = (SELECT MAX(date_taken) FROM
sb_logs WHERE device_id = 901));
Plain table query => explain analyze SELECT "iv_logs".* FROM "iv_logs"
WHERE (device_id = 1475 AND date_taken = (SELECT MAX(date_taken) FROM
iv_logs WHERE device_id = 1475));
sb_logs and iv_logs have identical index structure and similar cardinality
(about ~12.000.000 rows the first, ~9.000.000 rows the second).
sb_logs PLAN:
InitPlan 1 (returns $0)
-> Aggregate (cost=339424.47..339424.48 rows=1 width=8) (actual
time=597.742..597.742 rows=1 loops=1)
-> Append (cost=0.00..339381.68 rows=17114 width=8) (actual
time=42.791..594.001 rows=19024 loops=1)
-> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=8)
(actual time=0.000..0.000 rows=0 loops=1)
Filter: (device_id = 901)
-> Index Scan using
sb_logs_2012_on_date_taken_and_device_id on sb_logs_2012 sb_logs
(cost=0.00..319430.51 rows=16003 width=8) (actual time=42.789..559.165
rows=17817 loops=1)
Index Cond: (device_id = 901)
-> Index Scan using
sb_logs_2013_on_date_taken_and_device_id on sb_logs_2013 sb_logs
(cost=0.00..19932.46 rows=1106 width=8) (actual time=0.037..31.699
rows=1207 loops=1)
Index Cond: (device_id = 901)
-> Bitmap Heap Scan on sb_logs_2014 sb_logs
(cost=10.25..18.71 rows=4 width=8) (actual time=0.012..0.012 rows=0
loops=1)
Recheck Cond: (device_id = 901)
-> Bitmap Index Scan on
sb_logs_2014_on_date_taken_and_device_id (cost=0.00..10.25 rows=4 width=0)
(actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (device_id = 901)
-> Append (cost=0.00..26.86 rows=4 width=86) (actual
time=597.808..597.811 rows=1 loops=1)
-> Seq Scan on sb_logs (cost=0.00..0.00 rows=1 width=90) (actual
time=0.022..0.022 rows=0 loops=1)
Filter: ((device_id = 901) AND (date_taken = $0))
-> Index Scan using sb_logs_2012_on_date_taken_and_device_id on
sb_logs_2012 sb_logs (cost=0.00..10.20 rows=1 width=90) (actual
time=597.773..597.773 rows=0 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
-> Index Scan using sb_logs_2013_on_date_taken_and_device_id on
sb_logs_2013 sb_logs (cost=0.00..8.39 rows=1 width=91) (actual
time=0.011..0.011 rows=1 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
-> Index Scan using sb_logs_2014_on_date_taken_and_device_id on
sb_logs_2014 sb_logs (cost=0.00..8.27 rows=1 width=72) (actual
time=0.003..0.003 rows=0 loops=1)
Index Cond: ((date_taken = $0) AND (device_id = 901))
Total runtime: 598.049 ms
iv_logs PLAN:
Index Scan using index_iv_logs_on_date_taken_and_device_id on iv_logs
(cost=12.35..21.88 rows=1 width=157) (actual time=0.060..0.060 rows=1
loops=1)
Index Cond: ((date_taken = $1) AND (device_id = 1475))
InitPlan 2 (returns $1)
-> Result (cost=12.34..12.35 rows=1 width=0) (actual
time=0.053..0.053 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..12.34 rows=1 width=8) (actual
time=0.050..0.051 rows=1 loops=1)
-> Index Scan Backward using
index_iv_logs_on_date_taken_and_device_id on iv_logs (cost=0.00..261151.32
rows=21163 width=8) (actual time=0.046..0.046 rows=1 loops=1)
Index Cond: ((date_taken IS NOT NULL) AND
(device_id = 1475))
Total runtime: 0.101 ms
--
rd
This is the way the world ends.
Not with a bang, but a whimper.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
@ 2013-01-22 14:46 ` Andrew Dunstan <andrew@dunslane.net>
2013-01-22 15:08 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 18:38 ` Re: High CPU usage after partitioning Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Andrew Dunstan @ 2013-01-22 14:46 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; +Cc: pgsql-performance
On 01/22/2013 09:21 AM, rudi wrote:
> On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure <mmoncure@gmail.com
> <mailto:mmoncure@gmail.com>> 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 table.
>
> Partioned table query => explain analyze SELECT "sb_logs".* FROM
> "sb_logs" WHERE (device_id = 901 AND date_taken = (SELECT
> MAX(date_taken) FROM sb_logs WHERE device_id = 901));
>
And there you have it. Constraint exclusion does not work in cases like
this. It only works with static expressions (such as a literal date in
this case).
cheers
andrew
--
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] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:46 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
@ 2013-01-22 15:08 ` rudi <rudolone@gmail.com>
2013-01-22 15:42 ` Re: High CPU usage after partitioning Igor Neyman <ineyman@perceptron.com>
1 sibling, 1 reply; 11+ messages in thread
From: rudi @ 2013-01-22 15:08 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan <andrew@dunslane.net> 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 = 901 AND date_taken = (SELECT MAX(date_taken)
>> FROM sb_logs WHERE device_id = 901));
>>
>>
> And there you have it. Constraint exclusion does not work in cases like
> this. It only works with static expressions (such as a literal date in this
> case).
Ok, but I would have expected same plant repeated 4 times. When the table
is not partitioned, the plan is defintely smarter: it knows that index is
reversed and looks for max with an index scan backward). When the table is
partitioned, it scan forward and I guess it will always do a full index
scan.
--
rd
This is the way the world ends.
Not with a bang, but a whimper.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:46 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 15:08 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
@ 2013-01-22 15:42 ` Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 11+ messages in thread
From: Igor Neyman @ 2013-01-22 15:42 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; pgsql-performance
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=5 width=8) (actual time=0.021..0.021 rows=1 loops=1)"
" Index Cond: (cycle_date_time IS NOT NULL)"
" Heap Fetches: 0"
May be you should upgrade to 9.2.
Regards,
Igor Neyman
From: rudi [mailto:rudolone@gmail.com]
Sent: Tuesday, January 22, 2013 10:08 AM
To: pgsql-performance@postgresql.org
Subject: Re: High CPU usage after partitioning
On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan <andrew@dunslane.net<mailto:andrew@dunslane.net>> 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 = 901 AND date_taken = (SELECT MAX(date_taken) FROM sb_logs WHERE device_id = 901));
And there you have it. Constraint exclusion does not work in cases like this. It only works with static expressions (such as a literal date in this case).
Ok, but I would have expected same plant repeated 4 times. When the table is not partitioned, the plan is defintely smarter: it knows that index is reversed and looks for max with an index scan backward). When the table is partitioned, it scan forward and I guess it will always do a full index scan.
--
rd
This is the way the world ends.
Not with a bang, but a whimper.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: High CPU usage after partitioning
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 00:41 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Re: High CPU usage after partitioning Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` Re: High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-22 14:46 ` Re: High CPU usage after partitioning Andrew Dunstan <andrew@dunslane.net>
@ 2013-01-22 18:38 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2013-01-22 18:38 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: rudi <rudolone@gmail.com>; pgsql-performance
Andrew Dunstan <andrew@dunslane.net> 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_id = 901 AND date_taken = (SELECT
>> MAX(date_taken) FROM sb_logs WHERE device_id = 901));
> And there you have it. Constraint exclusion does not work in cases like
> this. It only works with static expressions (such as a literal date in
> this case).
This isn't about constraint exclusion I think. The main problem is in
the sub-select: 9.0 isn't able to index-optimize a MAX() across a
partitioned table, for lack of MergeAppend, so you end up scanning lots
of rows there. 9.1 or 9.2 should be better.
regards, tom lane
--
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] 11+ messages in thread
* Re: High CPU usage after partitioning
@ 2013-01-21 21:12 Kevin Grittner <kgrittn@mail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2013-01-21 21:12 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; rudi <rudolone@gmail.com>; +Cc: pgsql-performance
Merlin Moncure wrote:
>> I'm running postgresl 9.0. After partitioning a big table, CPU
>> usage raised from average 5-10% to average 70-80%.
> First thing that jumps to mind is you have some seq-scan heavy
> plans that were not seq-scan before.
Make sure that all indexes are defined for each partition. It is
not enough to define them on just the parent level.
-Kevin
--
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] 11+ messages in thread
end of thread, other threads:[~2013-01-22 18:38 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-21 15:05 High CPU usage after partitioning rudi <rudolone@gmail.com>
2013-01-21 17:13 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-22 00:41 ` Andrew Dunstan <andrew@dunslane.net>
2013-01-22 13:34 ` rudi <rudolone@gmail.com>
2013-01-22 14:04 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-22 14:21 ` rudi <rudolone@gmail.com>
2013-01-22 14:46 ` Andrew Dunstan <andrew@dunslane.net>
2013-01-22 15:08 ` rudi <rudolone@gmail.com>
2013-01-22 15:42 ` Igor Neyman <ineyman@perceptron.com>
2013-01-22 18:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-01-21 21:12 Re: High CPU usage after partitioning Kevin Grittner <kgrittn@mail.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