Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: Indexing UNIONs
Date: Mon, 15 Jul 2002 12:00:07 -0700
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207151200.07766.josh@agliodbs.com>

Folks,

I have two tables which are often browsed together through a UNION view, li=
ke:

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 tw=
o=20
tables (using a sequence).   However, as t1 has 100,000 records, it is=20
vitally important that queries against this view use an index.

As it is a Union view, though, they ignore any indexes:

jwnet=3D> explain select * from two_tables where id =3D 101072;
NOTICE:  QUERY PLAN:

Subquery Scan two_tables  (cost=3D0.00..3340.82 rows=3D99182 width=3D55)
  ->  Append  (cost=3D0.00..3340.82 rows=3D99182 width=3D55)
        ->  Subquery Scan *SELECT* 1  (cost=3D0.00..3339.81 rows=3D99181 wi=
dth=3D55)
              ->  Seq Scan on t1  (cost=3D0.00..3339.81 rows=3D99181 width=
=3D55)
        ->  Subquery Scan *SELECT* 2  (cost=3D0.00..1.01 rows=3D1 width=3D2=
8)
              ->  Seq Scan on t2  (cost=3D0.00..1.01 rows=3D1 width=3D28)

EXPLAIN
jwnet=3D> explain select * from t1 where id =3D 101072;
NOTICE:  QUERY PLAN:

Index Scan using t1_pkey on cases  (cost=3D0.00..5.99 rows=3D1 width=3D150)


How can I make this happen?  Ideas, suggestions?   And no, putting the data=
=20
from both tables into one is not an option for various schema reasons.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco