Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...

Do the number of columns in a particular table affect the performance of a query, when querying on a subset of that table?

For example, if table Foo has 20 columns, but my query only selects 5 of those columns, does having 20 (versus, say, 10) columns affect query performance? Assume for simplicity that anything in the WHERE clause is included in those 5 columns.

I'm concerned about the usage of Postgres' buffer cache in addition to the operating system's disk cache. I have a very lose understanding of Postgres' physical storage design. Tables are stored across several pages (defaulting to 8k in size per page), but I don't quite understand how tuples are arranged from there. Is P.G. smart enough to only fetch from disk the data that comprises those 5 columns?

3 Answers
3

The physical storage for rows is described in the docs in Database Page Layout. The column contents for the same row are all stored in the same disk page, with the notable exception of TOAST'ed contents (too large to fit in a page). Contents are extracted sequentially within each row, as explained:

To read the data you need to examine each attribute in turn. First
check whether the field is NULL according to the null bitmap. If it
is, go to the next. Then make sure you have the right alignment. If
the field is a fixed width field, then all the bytes are simply
placed.

In the simplest case (no TOAST'ed columns), postgres will fetch the entire row even if few columns are needed. So in this case, the answer is yes, having more columns may have a clear adverse impact on waster buffer cache, particularly if the column contents are large while still under the TOAST threshold.

Now the TOAST case: when an individual field exceeds ~2kB , the engine stores the field contents into a separate physical table. It also comes into play when the entire row doesn't fit into a page (8kB by default): some of the fields are moved to TOAST storage. Doc says:

If it's a variable length field (attlen = -1) then it's a bit
more complicated. All variable-length data types share the common
header structure struct varlena, which includes the total length of
the stored value and some flag bits. Depending on the flags, the data
can be either inline or in a TOAST table; it might be compressed, too

TOAST'ed contents are not fetched when they're not explicitly needed, so their effect on the total number of pages to fetch is small (a few bytes per column). This explains the results in @dezso's answer.

As for writes, each row with all its columns is entirely rewritten on each UPDATE, no matter what columns are changed. So having more columns is obviously more costly for writes.

Daniel's answer focuses on the cost of reading individual rows. In this context: defining columns NOT NULL helps a little. Putting fixed size NOT NULL columns first in your table helps a little. Putting relevant columns first (the ones you query for) helps a little. Minimizing padding (due to data alignment) by playing alignment tetris with your columns can help a little. But the most important effect has not been mentioned, yet, especially for big tables.

Additional columns obviously make a row cover more disk space, so that fewer rows fit on one data page (8 kB by default). Individual rows are spread out over more pages. The database engine generally has to fetch whole pages, not individual rows. It matters little whether individual rows are somewhat smaller or bigger - as long as the same number of pages has to be read.

If a query fetches a (relatively) small portion of a big table, where the rows are spread out more or less randomly over the whole table, it will result in roughly the same number of page reads, with little regard to row-size. Irrelevant columns will not slow you down much in such a (rare) case.

Most of the time, you will fetch patches or clusters of rows that have been entered in sequence or proximity and share data pages. As those rows are spread out due to the clutter, more disk pages have to be read to satisfy your query. Having to read more pages is typically the most important reason for a query to be slower. And that is the most important factor why irrelevant columns make your queries slower.

It may or may not matter if irrelevant columns are TOAST-ed. Relevant columns may be TOAST-ed as well, bringing back much of the same effect.

Limiting the query to the first 250 rows (WHERE num <= 250) results in 34.539 ms and 8.343 ms, respectively. Selecting all but long_long_text from this limited set results in 18.432 ms. This shows that in your terms PG is smart enough.

Well, I certainly appreciate the input. However, I can't say with certainty that this test scenario proves what I originally proposed. There are a few issues. For one, when you first run "SELECT * FROM test2", that should have filled your shared buffer cache. That query would have taken much longer to retrieve from disk. Thus, the 2nd query would theoretically have been much faster because it would have been fetching from S.B. cache. But I agree that it does 'suggest' that PG only fetches the rows it needs, based on your later tests/comparisons.
–
Jmoney38Jun 6 '12 at 11:50

You are right, this test (being simple) has its flaws. If I have enough time, I will try to cover these as well.
–
dezsoJun 6 '12 at 13:19