Re: Performance issues of one vs. two split tables. - Mailing list pgsql-general

From Bill Moseley
Subject Re: Performance issues of one vs. two split tables.
Date
Msg-id 20070515190246.GB3987@hank.org
Whole thread Raw
In response to Re: Performance issues of one vs. two split tables.  (Chris Browne <cbbrowne@acm.org>)
Responses Re: Performance issues of one vs. two split tables.
Re: Performance issues of one vs. two split tables.
Re: Performance issues of one vs. two split tables.
List pgsql-general
On Tue, May 15, 2007 at 01:42:18PM -0400, Chris Browne wrote:
> lists@peufeu.com (PFC) writes:
> >>     SELECT o.id
> >>     FROM order o
> >>     JOIN customer c on o.customer = c.id
> >>
> >> Does that bring into memory all columns from both order and customer?
> >> Maybe that's not a good example due to indexes.
> >
> >     No, it just pulls the columns you ask from the table, nothing
> > less,  nothing more.
>
> That's not quite 100% accurate.
>
> In order to construct the join, the entire pages of the relevant
> tuples in tables "order" and "customer" will need to be drawn into
> memory.
>
> Thus, if there are a whole bunch of columns on each table, the data in
> those extra columns (e.g. - all columns aside from "id", the one that
> was asked for in the result set) will indeed be drawn into memory.

Is that specific to Postgresql?  From an outside perspective it just
seems odd that potentially a large amount of data would be pulled off
disk into memory that is never used.  Perhaps there's an overriding
reason for this.

> If you alter tables "customer" and "order", taking some columns off,
> and stowing them in separate tables, then you'll find that more tuples
> of "customer" and "order" will fit into a buffer page, and that the
> join will be assembled with somewhat less memory usage.
>
> Whether or not that is a worthwhile change to make will vary
> considerably.

Makes designing the schema a bit tough. ;)

--
Bill Moseley
moseley@hank.org


pgsql-general by date:

Previous
From: SCassidy@overlandstorage.com
Date:
Subject: Re: Performance issues of one vs. two split tables.
Next
From: Steve Atkins
Date:
Subject: Re: Performance issues of one vs. two split tables.