Re: Indexing UNIONs

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.