[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] High CPU usage after partitioning
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2013-01-22 18:38:28
Message-ID: 24018.1358879908 () sss ! pgh ! pa ! us
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]