From: Julian Mehnle <julian@mehnle.net>
To: pgsql-performance@postgresql.org
Subject: Re: Bad query plans for queries on partitioned table
Date: Wed, 5 Dec 2007 01:57:25 +0000
User-Agent: KMail/1.9.7
References: <200712041944.13543.julian@mehnle.net>
	<200712042027.01921.julian@mehnle.net>
	<8763ze6kku.fsf@oxford.xeocode.com>
In-Reply-To: <8763ze6kku.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart14799584.8CLBGp1ukn";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200712050157.30907.julian@mehnle.net>

--nextPart14799584.8CLBGp1ukn
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Gregory Stark wrote:
> "Julian Mehnle" <julian@mehnle.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=20
manually combining separate SELECTs for each partition using UNION?

Julian.

--nextPart14799584.8CLBGp1ukn
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBHVgWKwL7PKlBZWjsRAssrAJ9u0auUU1Ib/Jfoz6yuyYIPfhqBHACg46QC
ztJbLvwsML2fN2cTM6S0LhE=
=9b7Q
-----END PGP SIGNATURE-----

--nextPart14799584.8CLBGp1ukn--