Re: Bad query plans for queries on partitioned table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Julian Mehnle" <julian@xxxxxxxxxx> writes:

> Gregory Stark wrote:
>> "Julian Mehnle" <julian@xxxxxxxxxx> 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!

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux