From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
cc: rudi <rudolone@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: High CPU usage after partitioning
In-reply-to: <50FEA640.10201@dunslane.net>
References: 
 <CAMxPiKHyJkMAqO62wH6aVzpvpfxhHd_P4pZZRgTCAdxLAnLeCw@mail.gmail.com>
	<50FDE039.3030601@dunslane.net>
	<CAMxPiKFW1VaMXo7HoH6fd3deQ1LJ4wU3DEAWG0sYjyg1nRq-_Q@mail.gmail.com>
	<CAHyXU0zgFTdf57=ZmK0ATG5yM37SYmBcwyaq4Psm+qi5ke6wZg@mail.gmail.com>
	<CAMxPiKF-O7C+LUdJuudZYXxdwgagoYMcgZNvsuTMmPTyXASm-w@mail.gmail.com>
	<50FEA640.10201@dunslane.net>
Comments: In-reply-to Andrew Dunstan <andrew@dunslane.net>
	message dated "Tue, 22 Jan 2013 09:46:24 -0500"
Date: Tue, 22 Jan 2013 13:38:28 -0500
Message-ID: <24018.1358879908@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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