On Tue, May 15, 2007 at 01:42:18PM -0400, Chris Browne wrote: > [EMAIL PROTECTED] (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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly