Re: Bad query plans for queries on partitioned table

"Julian Mehnle" <julian(at)mehnle(dot)net> writes:

> 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?

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!