From: Shaun Thomas <sthomas@optionshouse.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Partitions not Working as Expected
Date: Thu, 27 Jun 2013 11:15:43 -0500
Message-ID: <51CC652F.3010304@optionshouse.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hey guys,
I suspect I'll get an answer equivalent to "the planner treats that like
a variable," but I really hope not because it renders partitions
essentially useless to us. This is as recent as 9.1.9 and constraint
exclusion is enabled.
What I have is this test case:
CREATE TABLE part_test (
fake INT,
part_col TIMESTAMP WITHOUT TIME ZONE
);
CREATE TABLE part_test_1 (
CHECK (part_col >= '2013-05-01' AND
part_col < '2013-06-01')
) INHERITS (part_test);
CREATE TABLE part_test_2 (
CHECK (part_col >= '2013-04-01' AND
part_col < '2013-05-01')
) INHERITS (part_test);
And this query performs a sequence scan across all partitions:
EXPLAIN ANALYZE
SELECT * FROM part_test
WHERE part_col > CURRENT_DATE;
The CURRENT_DATE value is clearly more recent than any of the
partitions, yet it checks them anyway. The only way to get it to
properly constrain partitions is to use a static value:
EXPLAIN ANALYZE
SELECT * FROM part_test
WHERE part_col > '2013-06-27';
But developers never do this. Nor should they. I feel like an idiot even
asking this, because it seems so wrong, and I can't seem to come up with
a workaround other than, "Ok devs, hard code dates into all of your
queries from now on."
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
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: sthomas@optionshouse.com
Subject: Re: Partitions not Working as Expected
In-Reply-To: <51CC652F.3010304@optionshouse.com>
* 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