Re: Overhead of union versus union all

Simon Riggs wrote:
> or a query like this
>
> Select '1', ...
> ...
> union
> Select status, ...
> ...
> where status != '1';
> ;
>
> then it is clear that we could automatically prove that the the distinct
> step is redundant and so we could either hash or sort. This is the same
> as replacing the UNION with UNION ALL.

In the last example, how do you know that status != '1' produces unique
output? I assumed UNION gave distinct for the entire output, not just
remove duplicates from the two UNION branches; that's how Postgres
behaves now:

test=> SELECT 1 UNION (SELECT 2 UNION ALL SELECT 2);
?column?
----------
1
2
(2 rows)

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