Re: Overhead of union versus union all

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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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