Re: Index-only scans

On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote:

> Implementing index-only scans requires a few changes:

I would like to see a clear exposition of the use cases and an an
analysis of the costs and benefits of doing this. It sounds cool, but I
want to know it is cool before we spend time solving all of the juicy
problems. Perhaps a glue-and-string patch would help.

Extra buffer accesses for vismap, crash-safe vismap sound like
performance issues, as well as planner time, not to mention all the
tuits needed. Will it damage the general case?

Oracle-does-index-only-scans, true, but we have other overheads that
mean it may not be a slamdunk win for Postgres in every case. I dealt
with a company last year that insisted this was a requirement and yet
was resistant to any ideas of how the solution could be achieved in
other ways not available and not typically known to Oracle users.

The single SQL example mentioned already has at least two mechanisms for
improving performance of that type of query. We probably don't need
another, or at least we need a good analysis of why.

The benefit that occurs to me most is covered indexes, i.e. it opens up
new and interesting indexing strategies. Covered indexes are also one
kind of materialized view. It may be better to implement mat views and
gain wider benefits too. Or maybe index-only scans are mat views, via
some cunning plan?

No doubts about the implementation details if we do it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support