Re: Overhead of union versus union all

On Fri, 2009-07-10 at 09:28 -0400, Bruce Momjian wrote:
> 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.

None, that was not my point.

If you have a query like this

Select ..., status, ...
...
where status = '1'
union
Select ..., status, ...
...
where status = '2';

or a query like this

Select '1', ...
...
union
Select '2', ...
...
;

or a query like this

Select '1', ...
...
union
Select status, ...
...
where status != '1';
;

then it is clear that we could automatically prove that the the distinct
step is redundant and so we could either hash or sort. This is the same
as replacing the UNION with UNION ALL.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support