Re: Visibillity testing - some numbers on current performance.

On Tue, Apr 5, 2011 at 3:04 PM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
> I initially set out to put some numbers on "why" the visibillity
> map was important for "select count(*)", primarily to give some
> feedback to Simon Riggs stating:
> "Your tests and discussion remind me that I haven't yet seen any tests
> that show that index-only scans would be useful for performance."

I'm not sure what this has to do with index-only scans.

At least as I understand it, the concern about the way we do it now is
primarily that scanning the index will lead to random I/O on the
underlying table, which you aren't going to trigger with count(*).

I agree that the question about how much this benefits performance is
a worthwhile one. If your database is RAM cached I suspect it makes
very little difference. You might save something on MVCC visibility
checks and shared_buffers churn, but it probably won't be a lot.
Where I would expect to see a benefit is if the database is much
larger than available memory, and especially if the index fits but the
index+table doesn't. Now reading rows randomly from the index based
on a stream of many queries for the form SELECT a, b FROM foo WHERE a
= <some constant> ought to be much faster if you can look at the index
in memory and be done, and much slower if you have to read a heap
block from disk every time.

Now how we measure this without having built it is an interesting
question. There is probably some way of getting useful numbers out,
but I'm not sure I know what it is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company