From: Bruno Wolff III <bruno@wolff.to>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexing UNIONs
Date: Wed, 17 Jul 2002 21:29:01 -0500
Message-ID: <20020718022901.GA11498@wolff.to> (raw)
In-Reply-To: <200207171719.22224.josh@agliodbs.com>
References: <20020716114553.GB29323@wolff.to>
<200207161542.23433.josh@agliodbs.com>
<20020717120646.GA5520@wolff.to>
<200207171719.22224.josh@agliodbs.com>
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.
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: bruno@wolff.to, josh@agliodbs.com
Subject: Re: Indexing UNIONs
In-Reply-To: <20020718022901.GA11498@wolff.to>
* 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