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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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