Re: High CPU usage after partitioning - Mailing list pgsql-performance

From Tom Lane
Subject Re: High CPU usage after partitioning
Date
Msg-id 24018.1358879908@sss.pgh.pa.us
Whole thread Raw
In response to Re: High CPU usage after partitioning  (Andrew Dunstan <andrew@dunslane.net>)
List 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


pgsql-performance by date:

Previous
From: Igor Neyman
Date:
Subject: Re: High CPU usage after partitioning
Next
From: Виктор Егоров
Date:
Subject: Effect of the WindowAgg on the Nested Loop