From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Issue for partitioning with extra check constriants
Date: Fri, 01 Oct 2010 16:46:31 -0700
Message-ID: <4CA672D7.3070405@agliodbs.com> (raw)
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
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: Issue for partitioning with extra check constriants
In-Reply-To: <4CA672D7.3070405@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