Message-ID: <4DAF70FD.7000609@agliodbs.com>
Date: Wed, 20 Apr 2011 16:49:17 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Constraint exclusion can't process simple constant expressions?
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

All,

Apparently our CE is unable to deal with even moderately complex
expressions.  For example, given a CE check constraint of:

    "chk_start" CHECK (start >= '2011-01-31 00:00:00-05'::timestamp with
time zone AND start < '2011-03-01 00:00:00-05'::timestamp with time zone)

PostgreSQL CE is unable to figure out not to scan this partition for a
query which contains the following filter condition:

         WHERE start >= '2010-11-01'::timestamptz
           AND start < ('2010-11-30'::timestamptz + '1
day'::interval)::timestamptz

Even though it can figure out this one:

         WHERE call_start >= '2010-11-01'::timestamptz
           AND call_start < '2010-12-01'::timestamptz

I understand why now() is a problem for CE, but I'd expect that it could
at least handle a simple expression with immutable outputs.

We need a new form of partitioning ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com