From: | Tielman J de Villiers <tjdevil(at)bondnet(dot)co(dot)za> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Selecting across Multiple Tables |
Date: | 2001-12-05 12:44:33 |
Message-ID: | E1F206EC93DCD4119A05009027A41359064ACF@sbssvr.bondnet.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
This might be a general SQL question rather than a Postgres specific one.
Nevertheless, I hope someone can point me in the right direction.
We run a consumer site with one main table capturing all the client's data.
This works fine.
I am now rewriting the structure to rather insert/update/delete the details
in 10 little tables, all linked by a unique ID.
The problem is selecting ALL details from all 10 the tables in this kind of
format:
Select a,b,c,
d,e,
f,g,
...
>From 1,2,3,4,5,6,7,8,9,10
Where 1.id = (select last_value from sequence) and
2.id = 1.id and
3.id = 1.id and
4.id = 1.id and
5.id = 1.id and
...
When I developed the new application, it was on postgres 7.1.3, and
initially it caused a heavy load on the postmaster. This load was
substantially reduced by changing the where statement to:
...
Where 1.id = (select last_value from sequence) and
2.id = (select last_value from sequence) and
3.id = (select last_value from sequence) and
4.id = (select last_value from sequence) and
...
When I now tried the same on the live server, running postgres 6.5.3, and
any which way I try, I get an extremely heavy load on the postmaster -- with
the last test I had a (cost=737.78 rows=11 width=40) when selecting only 1
column from table 1!.
Am I misunderstanding the "relational" data model completely or selecting
wrongly?
Thank You
Tielman J de Villiers
BondNet Pty Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Colm McCartan | 2001-12-05 13:00:38 | Two very basic questions.. |
Previous Message | Raymond O'Donnell | 2001-12-05 12:25:32 | Re: Backends staying around |