Обсуждение: Subqueries or Joins? Problems with multiple table query

On 23 February 2010 11:44, Stefan Schwarzer
<stefan.schwarzer@grid.unep.ch> wrote:
> Hi there,
> gush, shouldn't be that complicated. But neither in Postgres, nor in Access
> I succeed in getting the result I wish.
> I have a couple of times for the Environmental Conventions (Kyoto, Montreal,
> CITES etc.). They look like this:
> id_country,year,value
> 4,1992,0
> 4,1993,0
> 4,1994,0
> 4,1995,0
> 4,1996,1
> 4,1997,0
> 4,1998,0
> 4,1999,0
> 4,2000,0
> so that I can see the year where the treaty has been ratified by a country.
> (The rows with the zero values seems to be unnecessary, I have to check that
> again with the data supplier.)
> Now, I would like to have a list of all (european) countries and the
> treaties they have signed, in the following style:
> country_name,year_kyoto,signed_kyoto,year_montreal,signed_montreal,....
>   Germany             1996               1               1992
>     1 ....
>   France                 1995              1               1994
>        1 ...
> Again, the field with the "signed_..." is not necessary, but I just want to
> be sure that the query is running correctly.
> I tried it with subqueries - something like this:
> 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
> (without the field "signed_..." then), but it seems not to be correct.
> I tried it as well with JOINs, but there, too, no success.
> Can anyone give me a hint?
> Thanks a lot,
> Stef

Is this what you're after?

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

Regards

Thom

In response to Stefan Schwarzer :
> Hi there,
>
> gush, shouldn't be that complicated. But neither in Postgres, nor in Access I
> succeed in getting the result I wish.
>
> I have a couple of times for the Environmental Conventions (Kyoto, Montreal,
> CITES etc.). They look like this:
>
> id_country,year,value
> 4,1992,0
> 4,1993,0
> 4,1994,0
> 4,1995,0
> 4,1996,1
> 4,1997,0
> 4,1998,0
> 4,1999,0
> 4,2000,0
>
> so that I can see the year where the treaty has been ratified by a country.
> (The rows with the zero values seems to be unnecessary, I have to check that
> again with the data supplier.)
>
> Now, I would like to have a list of all (european) countries and the treaties
> they have signed, in the following style:
>
> country_name,year_kyoto,signed_kyoto,year_montreal,signed_montreal,....
>   Germany             1996               1               1992
> 1 ....
>   France                 1995              1               1994
>    1 ...
>
> Again, the field with the "signed_..." is not necessary, but I just want to be
> sure that the query is running correctly.
>
> I tried it with subqueries - something like this:

Not sure if i understand you corrently, if not, provide more information
(table structure and data), if possible copy&paste - able.

Okay, let me try:

test=*# select * from country ;
 id |  name
----+---------
  1 | germany
  2 | use
  3 | france
(3 rows)

test=*# select * from conventions ;
 id_country | convention | year
------------+------------+------
          1 | Kyoto      | 1996
          1 | Montreal   | 2002
          2 | Kyoto      | 1998
(3 rows)

test=*# select c.name, sum(case when c2.convention='Kyoto' then c2.year
else null end) as kyoto, sum(case when c2.convention='Montreal' then
c2.year else null end) as montreal from country c left join conventions
c2 on c.id=c2.id_country group by c.name;
  name   | kyoto | montreal
---------+-------+----------
 germany |  1996 |     2002
 use     |  1998 |
 france  |       |
(3 rows)


(i know, silly and wrong data, only for example)


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

> 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.

On 23 February 2010 13:23, Stefan Schwarzer
<stefan.schwarzer@grid.unep.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?

You may also wish to review Andreas' suggestions as they propose a
more sensible table structure rather than having a table for each
convention.

You may also be interested in the tablefunc contrib module to help
present your data in the format you require rather than having a join
for each convention:
http://www.postgresql.org/docs/8.4/static/tablefunc.html

Thom

>>> 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


> You may also wish to review Andreas' suggestions as they propose a
> more sensible table structure rather than having a table for each
> convention.

The table proposal really looks nice. But our database is structured
by variable - so each convention has its own table.


> You may also be interested in the tablefunc contrib module to help
> present your data in the format you require rather than having a join
> for each convention:

Yes, I use this for the online representation of the data. Very useful
indeed.

On 23 February 2010 13:43, Stefan Schwarzer
<stefan.schwarzer@grid.unep.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?

Thom

In response to Stefan Schwarzer :
> >You may also wish to review Andreas' suggestions as they propose a
> >more sensible table structure rather than having a table for each
> >convention.
>
> The table proposal really looks nice. But our database is structured
> by variable - so each convention has its own table.

It is a really bad design - it can't scale. A new convention, and you
have to redesign your database schema (add a new table, rewrite code).

I think, it makes absolutely no sense to invest more work into this,
sorry, shit.

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

>>>>> 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?

Ok, seems to be a problem with the server and a running process or so.
On my local machine: great! Works fine. Thanks a lot for your help!

In response to Thom Brown :
> On 23 February 2010 13:43, Stefan Schwarzer
> <stefan.schwarzer@grid.unep.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

In response to A. Kretschmer :
> In response to Thom Brown :
> > On 23 February 2010 13:43, Stefan Schwarzer
> > <stefan.schwarzer@grid.unep.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:

Ouch, i'm wrong, sorry.

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

>>> You may also wish to review Andreas' suggestions as they propose a
>>> more sensible table structure rather than having a table for each
>>> convention.
>>
>> The table proposal really looks nice. But our database is structured
>> by variable - so each convention has its own table.
>
> It is a really bad design - it can't scale. A new convention, and you
> have to redesign your database schema (add a new table, rewrite code).
>
> I think, it makes absolutely no sense to invest more work into this,
> sorry, shit.

Thanks for the feedback. We spent a couple of weeks analyzing the
possible solutions for our Portal. And, with lots of feedback from
this group, we decided to go for what we've now. It doesn't seem that
much "scalable"... but in general, with everything we have to do -
preparation of the data, updating of the data etc. - it seems to us
actually as a very smooth solution.

Thanks for your help and your ideas!