Received: from wolff.to (wolff.to [66.92.219.49])
	by postgresql.org (Postfix) with SMTP id E876E475910
	for <pgsql-sql@postgresql.org>; Wed, 17 Jul 2002 22:23:10 -0400 (EDT)
Received: (qmail 11630 invoked by uid 500); 18 Jul 2002 02:29:01 -0000
Date: Wed, 17 Jul 2002 21:29:01 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexing UNIONs
Message-ID: <20020718022901.GA11498@wolff.to>
Mail-Followup-To: Josh Berkus <josh@agliodbs.com>,
	pgsql-sql@postgresql.org
References: <20020716114553.GB29323@wolff.to>
	<200207161542.23433.josh@agliodbs.com>
	<20020717120646.GA5520@wolff.to>
	<200207171719.22224.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200207171719.22224.josh@agliodbs.com>
User-Agent: Mutt/1.3.25i
X-Archive-Number: 200207/271
X-Sequence-Number: 8649

Just in case there was some misunderstanding of my suggestion here is
what I had in mind.

Your query:
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;

My suggestion:
SELECT t3.id, coalesce(t1.name, t2.name), t1.abbreviation,
  coalesce(t1.juris_id, t2.juris_id) from
  (t3 left join t1 using (id)) left join t2 using (id);

t3 is the event table.
This will result in one row for each row in t3 (since id is unique accross
t1 and t2). It will contain the name, juris_id and abbreviation from
whichever table matched. I expect the query to be able to make use of
indexes in this form, though I haven;t tested it to make sure.