Multiple outer join on same table

От:

Marco Lazzeri <marcomail@noze.it>

Дата:

9 февраля 2004 г. в 15:08:20

Hi!
I'm searching a better (quicker) way to retrieve data as I used to do
using the following query...

==
SELECT   main.codice,   other.value AS value_one,   other.value AS value_two
FROM main LEFT OUTER JOIN other                        ON main.id =
other.id_main                     LEFT OUTER JOIN other AS other2 ON main.id =
other2.id_main
WHERE       other.type = 'type_one'
AND other2.type = 'type_two';
==

Thanks,
Marco

Re: Multiple outer join on same table

От:

Markus Bertheau <twanger@bluetwanger.de>

Дата:

11 февраля 2004 г. в 17:24:07

On Пнд, 2004-02-09 at 19:12 +0100, Marco Lazzeri wrote:
> Hi!
> I'm searching a better (quicker) way to retrieve data as I used to do
> using the following query...
> 
> ==
> SELECT
>     main.codice,
>     other.value AS value_one,
>     other.value AS value_two
> FROM main LEFT OUTER JOIN other                        ON main.id =
> other.id_main
>                       LEFT OUTER JOIN other AS other2 ON main.id =
> other2.id_main
> WHERE
>         other.type = 'type_one'
> AND other2.type = 'type_two';
> ==

You can alias a table to join it to itself. I'm not sure whether that
answers your question.

SELECT t1.field FROM table AS t1 JOIN table as t2 on (cond)

-- 
Markus Bertheau

Re: Multiple outer join on same table

От:

Greg Stark <gsstark@mit.edu>

Дата:

14 мая 2004 г. в 02:40:52

Marco Lazzeri  writes:

> Hi!
> I'm searching a better (quicker) way to retrieve data as I used to do
> using the following query...
> 
> ==
> 
> SELECT main.codice,
>        other.value AS value_one,
>        other.value AS value_two
>   FROM main 
>   LEFT OUTER JOIN other           ON (main.id = other.id_main)
>   LEFT OUTER JOIN other AS other2 ON (main.id = other2.id_main)
>  WHERE other.type = 'type_one'
>    AND other2.type = 'type_two'
> ;

a) you're better off sending the actual query rather than retyping it. I
assume you made a typo in the select column list and it should be
"other2.value AS value_two"? Also the parentheses are required on the ON
clause.

b) The WHERE clause will effectively make this a plain inner join, not an
outer join at all. Since any values that aren't found would have a NULL type
column and cause the row to not be selected.

I think the query you meant to write would be

SELECT codice,       other1.value AS value_one,       other2.value AS value_two FROM main LEFT OUTER JOIN other as other1 ON (main.id = other1.id_main AND type = 'type_one') LEFT OUTER JOIN other as other2 ON (main.id = other2.id_main AND type = 'type_two)

Another way to write this query that might be faster or might not depending
would be:

SELECT codice,      (SELECT value FROM other WHERE id_main = id AND type = 'type_one') AS value_one,      (SELECT value FROM other WHERE id_main = id AND type = 'type_two') AS value_two FROM codice

In theory the two queries really ought to always result in the same plan
because they're equivalent. However the Postgres optimizer as clever as it is
is incapable of seeing this. 

The first form with the outer join leaves the optimizer with a lot more
flexibility though, including at least one plan that is effectively identical
to what the optimizer is forced to do for the second query. So really the
first one should be no worse than the second.

If you find the second faster (or if they're both still slow) you might
consider posting explain analyze output for both queries. It may be that you
have other issues preventing the optimizer from finding a good plan.

You have run analyze on these tables recently? And you vacuum regularly? And
for the second query you would really want an index on other.id_main too. For
the first one it would depend on the data in the two tables.

-- 
greg