Message-ID: <4CA672D7.3070405@agliodbs.com>
Date: Fri, 01 Oct 2010 16:46:31 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Issue for partitioning with extra check constriants
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Simon, Greg, etc.,

Just barked my nose against a major performance issue with CE &
partitioning, and was wondering if anyone had poked at it.

The issue is this: when a partitioned table is evaluated by the planner
for constraint exclusion, it evaluates ALL check constraints on each
partition, regardless of whether or not they include a referenced column
in the query (and whether or not they relate to partitioning).  If some
of those check constraints are expensive (like GIS functions) then this
can add considerably (on the order of 2ms per partition) to planning time.

If this is news to anyone, I have a nice test case.

So ... how plausible is it to fix the planner so that it only evaluates
check constraints on a partition if there is a match of referenced
columns?  Are we talking "moderate", "hard" or "nearly impossible"?

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