Message-ID: <51CC652F.3010304@optionshouse.com>
Date: Thu, 27 Jun 2013 11:15:43 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Partitions not Working as Expected
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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