Re: Overhead of union versus union all

On Thu, Jul 9, 2009 at 7:58 PM, Bruce Momjian<bruce@momjian.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?