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