From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Indexing UNIONs |
Date: | 2002-07-15 19:00:07 |
Message-ID: | 200207151200.07766.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.81 rows=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 Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-07-15 20:12:52 | Re: Indexing UNIONs |
Previous Message | Chad Thompson | 2002-07-15 18:45:58 | Seeding |