From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: rudi <rudolone@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: High CPU usage after partitioning
Date: Tue, 22 Jan 2013 13:38:28 -0500
Message-ID: <24018.1358879908@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, andrew@dunslane.net, rudolone@gmail.com
Subject: Re: High CPU usage after partitioning
In-Reply-To: <24018.1358879908@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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