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

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.

They may not be drawn into the return set, but they will still be
drawn into memory.

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.
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://linuxdatabases.info/info/languages.html
There are two kinds of pedestrians -- the quick and the dead.