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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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