On 7/9/07, Mike <akiany@xxxxxxxxx> wrote:
I am designing my database and I was wondering whether my table row size effects the performance of querying my table. Please note that my table is being designed to hold high volume of records and I do not plan to do (select *) for retrieving them. That is I plan to only query a few of those fields at a given time but each row contains significantly more data that are not being queried at the time.
Obvious observation: Since PostgreSQL's unit of data storage is the page, selects -- even on single attributes -- result in entire pages being loaded into memory and then read. Since the cache (PostgreSQL's shared buffers plus the OS file system cache) holds pages, not individual attributes, more data per tuple (row) means fewer tuples to fit in the cache. As far as the CPU cache goes, as I understand it, the fact that you're reading just a few attributes from each tuple (maybe even just a few from each page) is inefficient -- you will be forcing lots of data into the cache that is never used. In general, you might be better off normalizing your table, if possible, or partitioning it into subtables. But these are the broad strokes -- how many columns are we talking about exactly, and of what data types? Alexander.
