Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Actually, in the type of case Mark is showing, the estimates might
> be *more* accurate since the condition gets decomposed into
> separate per-table conditions. I'm still dubious about how often
> it's a win though.
>
> There's another problem, which is that transforming to UNION isn't
> necessarily a safe transformation: it only works correctly if the
> query output columns are guaranteed unique. Otherwise it might
> fold duplicates together that would have remained distinct in the
> original query. If your query output columns include a primary
> key then the planner could be confident this was safe, but that
> reduces the scope of the transformation even further ...
FWIW, I've seen this optimization in other products. I remember
being surprised sometimes that it wasn't used where I thought it
would be, and I had to explicitly transform the query to UNION to
get the performance benefit. That was probably due to the sort of
constraints you mention on when it is truly equivalent.
Personally, I'd put this one in the "it would be nice if" category.
Does it merit a TODO list entry, perhaps?
-Kevin