On Tue, Apr 5, 2011 at 3:04 PM, Jesper Krogh <jes...@krogh.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers