Partitions not Working as Expected

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux