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