Indexing UNIONs

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

Responses

Browse pgsql-sql by date

  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