Re: Optimizer oddness, possibly compounded in 8.1

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Philip Warner <pjw(at)rhyme(dot)com(dot)au>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Optimizer oddness, possibly compounded in 8.1
Date: 2005-12-03 09:21:37
Message-ID: 1133601697.2906.721.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, 2005-12-02 at 19:43 -0500, Tom Lane wrote:
> Philip Warner <pjw(at)rhyme(dot)com(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Philip Warner 2005-12-03 11:06:40 Re: Optimizer oddness, possibly compounded in 8.1
Previous Message Simon Riggs 2005-12-03 09:10:54 Re: Reducing relation locking overhead