Re: Indexing UNIONs

On Mon, 15 Jul 2002, Josh Berkus wrote:

> Stephan,
>
> > We had a discussion recently on -general about this. Right now the
> > planner won't push the conditions down into the arms of the union because
> > noone's been sure under what conditions the optimization is safe.
>
> So, if performance is horrible with the view, I should use a dummy table to
> hold the Unioned data and index that instead?

Possibly.

> I can understand the difficultyof optimization. However, the example I
> supplied is the simplest of unions, and the two Seq Scans do seem to be
> proceeding against each table seperately. I think for very simple Unions
> (i.e. no grouping, no filtering within subqueries, etc.) that index usage
> would be reasonable to implement.

I don't think it's a difficulty of implementation thing. I'd guess that
alot of the current stuff for shoving down conditions would apply (I
haven't looked, though). It's more a case of making sure the optimization
cannot be a false one that changes the results. What we need is someone
to sit down and analyze the cases in a serious way.

I think that for union all, conditions other than non-stable ones can be
pushed down. For union, I think there might be issues due to the removal
of duplicates in certain cases where the results will change, but that the
results may not be deterministic in such cases anyway (like a case where
two values are not exactly the same but aren't distinct due to collation
or some such and so the system picks an arbitrary one and that arbitrary
one affects the output of query). I have no good idea for EXCEPT and
INTERSECT.