Skip site navigation (1) Skip section navigation (2)

Re: Bad query plans for queries on partitioned table

From: Julian Mehnle <julian(at)mehnle(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bad query plans for queries on partitioned table
Date: 2007-12-05 01:57:25
Message-ID: 200712050157.30907.julian@mehnle.net (view raw or flat)
Thread:
Lists: pgsql-performance
Gregory Stark wrote:
> "Julian Mehnle" <julian(at)mehnle(dot)net> writes:
> > I actually do have constraints on all the partitions, e.g. for week
> > 34: [...]
> >
> > 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.

That would be great.

So there's nothing that can be done about it right now, apart from 
manually combining separate SELECTs for each partition using UNION?

Julian.

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2007-12-05 06:47:04
Subject: Re: RAID arrays and performance
Previous:From: Mark MielkeDate: 2007-12-05 00:55:03
Subject: Re: RAID arrays and performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group