Re: OR vs UNION vs UNION ALL

On Wed, Sep 23, 2015 at 8:38 AM, Etienne Champetier
<champetier.etienne@gmail.com> wrote:
> Two questions:
> 1) Is it normal to have such a big difference between OR and UNION and
> should i always prefer UNION when possible?

yes, it's unfortunate but true.  I consider automatic transformation
of these expressions to possible enhancements to the planner.
Although the fact it hasn't already been done hints at high difficulty
or complicated trade-offs.

Note, these queries are not the same.  OR/UNION ALL should give the
same result regardless of input but UNION has a deduplication step
that gives a different answer.  Avoid pure UNION unless it's very
specifically what you want; it can act as an optimization fence in
more complex queries.

> 2) How can UNION ALL be slower than UNION, it's not doing the HashAggregate
> but is 0.2s slower?

likely measurement noise.  take 10 samples and take a median.

merlin