Re: EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 1, 2016 at 9:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Maybe, but neither UNION nor UNION ALL would duplicate the semantics
>> of OR, so there's some handwaving here that I missed.

> SELECT * FROM foo WHERE a = 5 OR a = 4
> isn't equivalent to
> SELECT * FROM foo WHERE a = 5
> UNION
> SELECT * FROM foo WHERE a = 4
> ?

It probably is, but you're assuming that "a" appears in the list of
columns being unioned. If you make that just "SELECT b FROM ..."
then the latter form gets rid of duplicate b values where the first
doesn't. On the other hand, UNION ALL might introduce duplicates
not present in the OR query's result.

regards, tom lane