Re: Indexing UNIONs

Поиск
Список
Период
Сортировка
От Bruno Wolff III
Тема Re: Indexing UNIONs
Дата
Msg-id 20020718022901.GA11498@wolff.to
обсуждение исходный текст
Ответ на Re: Indexing UNIONs  (Josh Berkus <josh@agliodbs.com>)
Ответы Re: Indexing UNIONs
Список pgsql-sql
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.


В списке pgsql-sql по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Indexing UNIONs
Следующее
От: "Rajesh Kumar Mallah." (by way of Rajesh Kumar Mallah.
Дата:
Сообщение: Re: Cascading deletions does not seem to work inside PL/PGSQL functions.