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@xxxxxxxxxxxxxxxx
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
