From: Gregory Stark <stark@enterprisedb.com>
To: Julian Mehnle <julian@mehnle.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plans for queries on partitioned table
Date: Wed, 05 Dec 2007 00:39:45 +0000
Message-ID: <8763ze6kku.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <200712042027.01921.julian@mehnle.net>
References: <200712041944.13543.julian@mehnle.net>
<87ir3e6wti.fsf@oxford.xeocode.com>
<200712042027.01921.julian@mehnle.net>
"Julian Mehnle" <julian@mehnle.net> writes:
> I actually do have constraints on all the partitions, e.g. for week 34:
>
> Check constraints [for email_2007_week34]:
> "email_2007_week34_ts_check" CHECK (ts >= '2007-08-20 00:00:00'::timestamp without time zone AND ts < '2007-08-27 00:00:00'::timestamp without time zone)
>
> Check constraints [for email_2007_week34_extras]:
> "email_2007_week34_extras_ts_check" CHECK (ts >= '2007-08-20 00:00:00'::timestamp without time zone AND ts < '2007-08-27 00:00:00'::timestamp without time zone)
>
> Shouldn't this be enough to give the query planner a clue that it only
> has to join the "email" and "email_extras" tables' partitions pair-wise,
> as opposed to cross-joining them?
Ah, well, this falls under "The optimizer is a bit dumb about partitioned
tables". It only looks at the constraints to compare against your WHERE
clause. It doesn't compare them against the constraints for other tables to
see if they're partitioned on the same key and therefore can be joined
table-by-table.
I want 8.4 to be cleverer in this area but there's a ton of things it has to
learn.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
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: stark@enterprisedb.com, julian@mehnle.net
Subject: Re: Bad query plans for queries on partitioned table
In-Reply-To: <8763ze6kku.fsf@oxford.xeocode.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