Selecting across Multiple Tables - Mailing list pgsql-general

From Tielman J de Villiers
Subject Selecting across Multiple Tables
Date
Msg-id E1F206EC93DCD4119A05009027A41359064ACF@sbssvr.bondnet.co.za
Whole thread Raw
List 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

pgsql-general by date:

Previous
From: "Raymond O'Donnell"
Date:
Subject: Re: Backends staying around
Next
From: Colm McCartan
Date:
Subject: Two very basic questions..