Re: Overhead of union versus union all

Simon Riggs wrote:
>
> On Thu, 2009-07-09 at 20:41 -0600, Scott Marlowe wrote:
> > On Thu, Jul 9, 2009 at 7:58 PM, Bruce Momjian<bruce(at)momjian(dot)us> wrote:
> > > Scott Bailey wrote:
> > >> Alvaro Herrera wrote:
> > >> > Tim Keitt wrote:
> > >> >> I am combining query results that I know are disjoint. I'm wondering
> > >> >> how much overhead there is in calling union versus union all. (Just
> > >> >> curious really; I can't see a reason not to use union all.)
> > >> >
> > >> > UNION needs to uniquify the output, for which it plasters an additional
> > >> > sort step, whereas UNION ALL does not need to uniquify its output and
> > >> > thus it can avoid the sort step. Using UNION ALL is recommended
> > >> > wherever possible.
> > >> >
> > >> I think I read somewhere that as of 8.4 it no longer required the sort
> > >> step, due to the improvements in hashing. Here it is
> > >>
> > >> http://wiki.postgresql.org/wiki/WhatsNew84#Performance
> > >
> > > Oh, yea, hashing is used in some cases rather than sort. I assume sort
> > > is still used if the hash exceeds workmem size.
> >
> > The important point being that it's still more expensive than a plain
> > union all thought, right?
>
> 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.

--
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. +