Partitions not Working as Expected - Mailing list pgsql-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