From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Indexing UNIONs
Date: Mon, 15 Jul 2002 12:00:07 -0700
Message-ID: <200207151200.07766.josh@agliodbs.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Indexing UNIONs
In-Reply-To: <200207151200.07766.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox