From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Constraint exclusion can't process simple constant expressions?
Date: Wed, 20 Apr 2011 16:49:17 -0700
Message-ID: <4DAF70FD.7000609@agliodbs.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Constraint exclusion can't process simple constant expressions?
In-Reply-To: <4DAF70FD.7000609@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox