Re: Overhead of union versus union all

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Scott Bailey <artacus(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Overhead of union versus union all
Date: 2009-07-10 01:58:17
Message-ID: 200907100158.n6A1wHU07186@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart McGraw 2009-07-10 02:00:12 psql language
Previous Message Scott Bailey 2009-07-10 01:55:31 Re: Overhead of union versus union all