Sure. It's not the default already because the functionality did not exist at all in previous versions.

Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like:

  select column4 from table where column3 = x;

the DB will only read from the index (and not look at the table at all.) This is much faster as the index is much smaller in size than the table.

Thanks, that's interesting. So it only kicks in when your index covers all the columns in the "what" (select) clause. That tends not to be true for tables I've designed/worked with. I guess you could index differently to take advantage of this though.

Yes, databases which support index-only scans tend to encourage this pattern of adding extra columns in your indexes. In some cases this can be a big gain, but larger indexes means that the indexes are slower for queries which do not need the extra columns or need to read the entire row anyway.

Another set of queries which benefit from index only scans are those with EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they always needed to read part of the actual row to check the visibility information. In 9.2 it will for VACUUMED pages only need to check the index and the visibility map.

It's not the default already because pre-9.2 the MVCC implementation in Postgres didn't guarantee that an index has the same visibility as the table data.

http://rhaas.blogspot.com/2010/11/index-only-scans.html