Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 357E72E0193 for ; Wed, 5 Dec 2007 04:39:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 43936-03 for ; Wed, 5 Dec 2007 04:39:25 -0400 (AST) Received: from oxford.xeocode.com (unknown [87.127.95.196]) by postgresql.org (Postfix) with ESMTP id 1C89F2E00B3 for ; Wed, 5 Dec 2007 04:39:28 -0400 (AST) Received: from localhost.enta.net ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1Izpmv-0003p7-SX; Wed, 05 Dec 2007 08:39:25 +0000 To: "Julian Mehnle" Cc: Subject: Re: Bad query plans for queries on partitioned table In-Reply-To: <200712050157.30907.julian@mehnle.net> (Julian Mehnle's message of "Wed\, 5 Dec 2007 01\:57\:25 +0000") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 3485) References: <200712041944.13543.julian@mehnle.net> <200712042027.01921.julian@mehnle.net> <8763ze6kku.fsf@oxford.xeocode.com> <200712050157.30907.julian@mehnle.net> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 05 Dec 2007 08:39:25 +0000 Message-ID: <87sl2h5yde.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/67 X-Sequence-Number: 27883 "Julian Mehnle" writes: > Gregory Stark wrote: >> "Julian Mehnle" 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? Well the in the query you gave I think if the partitions weren't completely empty it would still be using the hash join, it would just be doin an append of all the nearly-empty partitions first. The reason it's getting confused is that in the absence of stats on them it thinks they contain hundreds of tuples which will match your where clause and join clause. Look at the expected number of rows the for the merge jjoin compared to the expected number of rows for the hash join. But yeah, there will be cases where you really want: Append Merge Join Part1 of table1 Part2 of table2 Merge Join Part2 of table1 Part2 of table2 ... But the planner only knows how to do: Merge Join Append Part1 of table1 Part2 of table1 ... Append Part1 of table1 Part2 of table2 ... Which requires two big sorts whereas the first plan could use indexes on individual partitions. It also has a slower startup time and can't take advantage of discovering that a partition of table1 is empty to avoid ever reading from the corresponding partition of table2 the way the first plan can. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!