Re: View vs. Statement Query Plan

On Wed, Jun 05, 2002 at 10:49:46AM -0400, Gregory Seidman wrote:
> As long as you aren't messing with the columns (no projections, no value
> modifications, no aggregation), it is $\sigma_p(A) \cup \sigma_p(B)$ vs.
> $\sigma_p(A \cup B)$. These are equivalent, regardless of whether it is a
> proper UNION or a UNION ALL.

Well, i think in this users case he was using a view. Unless you are using
all the columns from the view you're going to have a problem, since dropping
columns is also a projection.

select a from (select a,b from tableA  union  select a,b from tableB)
(select a from tableA) union (select a from tableB)

are not equivalent. I don't think it's possible to simplify the first
statement at all with UNION. OTOH, UNION ALL would be fine.

Anyhow, for a programmtical point of view, all that would to be done would
be to allow certain types of projections to be distributed across Append
nodes. Basically, projections that drop fields and only involve cachable
functions. But you need to think harder about what can be passed through
Distinct nodes.

Proving that it works for straight Append is easy. First it does one query
and then the other. So any projections done just after can also be done
just before with the same effect. Although fields get renamed, don't they.

So when people ask why their view is slow, ask whether they mean UNION or
UNION ALL and change it if it makes no difference.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.