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

>> 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.

    Yeah, I wanted to mean that ;)
    All the columns are loaded (except the TOASTed ones which are not
mentioned in the query) into memory, but only the requested ones are
processed and returned to the client...

> Is that specific to Postgresql?

    Nope. All databases do more or less the same.

> 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.

    Yeah, where would you put this data if you didn't put it where it is now ?

>> 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. ;)

    "Premature optimization is the root of all evil"

    Build a test database, fill it with data, and experiment.