Re: Optimizer oddness, possibly compounded in 8.1

On Fri, 2005-12-02 at 19:43 -0500, Tom Lane wrote:
> Philip Warner <pjw@rhyme.com.au> writes:
> >> (The larger point that joins of inheritance unions aren't well-planned
> >> is true, but it's always been true...)
> 
> > It also seems to have a probkem with unions in views.
> 
> > Is there anything that can be done about this -- workarounds etc? Any
> > plans to address it? We've got a couple of places where it's beginning
> > to bite us due to growth of tables.
> 
> It's something that's on the ever-growing TODO list ... I dunno if
> anyone has any near-term plans to work on it.  It'd definitely be
> nice to teach the planner to do joins-over-unions well, and then
> make inheritance just invoke that behavior instead of being a crocky
> special case.

There's a number of things that can be pushed down over a union set, in
certain circumstances. sorts, GROUPs, min/max/limit, joins etc.. so I've
been mulling over a generic push-down mechanism to avoid lots of special
cases emerging. Joins to a union set are also an interesting case.

First off, I think we need to do some more work on partitioning so that
some knowledge about the union set is understood by the optimizer. At
the moment there is no concept of partition key, so its hard to spot
when two union sets have the same key to allow pushdown.

I hope to work on some of that in the 8.2 timebox, but I suspect this is
a multi-year mission.

Best Regards, Simon Riggs