Re: Indexing UNIONs

On Mon, Jul 15, 2002 at 17:31:24 -0700, Josh Berkus <josh@agliodbs.com> 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?

It wouldn't have to be a dummy table. You could have both sets of data
in the same table. Since they seem to be related enough that you went to
the trouble to give them compatible primary keys this may not be
inappropiate (though you must have had some reason for keeping them separate).
You can use a flag to indicate what the data type is. If you need fast
access to the smaller part of the table, a partial index might work.
If the column that didn't apply to the one table is always not null in the
other table, you could use is null on that column as your flag.