Re: Subqueries or Joins? Problems with multiple table query

In response to Thom Brown :
> On 23 February 2010 13:43, Stefan Schwarzer
> <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> wrote:
> >>>> Select countries.name, basel.year, basel.value, cites.year, cites.value
> >>>> From countries
> >>>> Left Join basel on basel.id_country = countries.id_country and
> >>>> basel.value=1
> >>>> Left Join cites on cites.id_country = countries.id_country and
> >>>> cites.value=1
> >>>
> >>> I would have thought so, but the query turns forever.
> >>
> >> How many rows in each of your tables, and what indexes do you have?
> >
> > around 5000 rows
> >
>
> It takes a long time for just 5000 rows? Could you provide the
> execution plan for it by putting EXPLAIN ANALYZE at the front of your
> query and post the result here?

I think, this query:

SELECT countries.name, (SELECT yearAS basel FROM basel WHERE value = 1
AND countries.id = basel.id_country) AS basel, (SELECT yearAS cites FROM
cites WHERE value = 1 AND countries.id = cites.id_country) AS cites FROM
countries, basel, cites

(copy & paste from his post) will produce a cross-join:

test=*# select count(1) from (select c.name, (select name from con_1 where id=c.id), (select name from con_2 where id=c.id), (select name from con_3 where id=c.id) from con c) foo;
count
-------
3
(1 row)

test=*# select count(1) from (select c.name, (select name from con_1 where id=c.id), (select name from con_2 where id=c.id), (select name from con_3 where id=c.id) from con c, con_1, con_2, con_3) foo;
count
-------
54
(1 row)

(the first query is similar to his query but without all tables in the enclosing from-list,
the second query is very similar to his query)

So i'm not astonished about a long time for only 5000 rows...

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99