Re: Performance issues of one vs. two split tables.

On Tue, May 15, 2007 at 01:42:18PM -0400, Chris Browne wrote:
> lists(at)peufeu(dot)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(at)hank(dot)org