Re: Indexing UNIONs

On Mon, 15 Jul 2002, Josh Berkus wrote:

> Folks,
>
> I have two tables which are often browsed together through a UNION view, like:
>
> CREATE VIEW two_tables AS
> SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id
> FROM t1
> UNION ALL
> SELECT t2.id, t2.name, NULL, t2.juris_id
> FROM t2;
>
> This works fine as a view, since I have made the id's unique between the two
> tables (using a sequence). However, as t1 has 100,000 records, it is
> vitally important that queries against this view use an index.

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.