Re: Overhead of union versus union all

Simon Riggs wrote:
>
> On Fri, 2009-07-10 at 08:59 -0400, Bruce Momjian wrote:
>
> > > I think it should be possible to use predtest theorem proving to
> > discard
> > > the sort/hash step in cases where we can prove the sets are
> > disjoint.
> > > Often there are top-level quals that can be compared in the WHERE
> > > clauses of the sub-queries, so a shallow search could be quite
> > > profitable in allowing us to rewrite a UNION into a UNION ALL.
> >
> > I assume we would still need the distinct removal step; we just avoid
> > the sort/hash.
>
> I mean it seems possible to prove that the distinct removal step is not
> necessary, by proving that the various sub-queries are already disjoint.
> It's a common manual optimization, so automating it seems a reasonable
> future goal.

I am confused what sub-queries produce _distinct_ output. I know there
are some that produce _ordered_ output.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +