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.
As it is a Union view, though, they ignore any indexes:
jwnet=> explain select * from two_tables where id = 101072;
NOTICE: QUERY PLAN:
Subquery Scan two_tables (cost=0.00..3340.82 rows=99182 width=55) -> Append (cost=0.00..3340.82 rows=99182 width=55)
-> Subquery Scan *SELECT* 1 (cost=0.00..3339.81 rows=99181 width=55) -> Seq Scan on t1
(cost=0.00..3339.81rows=99181 width=55) -> Subquery Scan *SELECT* 2 (cost=0.00..1.01 rows=1 width=28)
-> Seq Scan on t2 (cost=0.00..1.01 rows=1 width=28)
EXPLAIN
jwnet=> explain select * from t1 where id = 101072;
NOTICE: QUERY PLAN:
Index Scan using t1_pkey on cases (cost=0.00..5.99 rows=1 width=150)
How can I make this happen? Ideas, suggestions? And no, putting the data
from both tables into one is not an option for various schema reasons.
--
-Josh BerkusAglio Database SolutionsSan Francisco