Re: Indices for select count(*)?

On Thu, Dec 22, 2005 at 08:52:08AM -0600, Jim C. Nasby wrote:
> Back when I was using other databases more often, it wasn't uncommon to
> see a 10x speed improvement on count(*) from using an index. This is an
> area where PostgreSQL is seriously behind other databases. Of course
> having vastly superior concurrency goes a long way towards offsetting
> that in the real world, but it would be a Good Thing if we could get
> some form of tuple visibility into indexes, as has been discussed in the
> past.

Actually, ISTM the trend is going the other way. MySQL has instant
select count(*), as long as you're only using ISAM. Recent versions of
MSSQL use an MVCC type system and it also scans the whole table. Oracle
is the only one I've found that has any optimisation on this front.

The thing is, it *is* possible to change PostgreSQL to do counts via
the index. The problem is, the cost is high enough that we're
reasonably sure most people don't want to pay it. I've neverneeded an
exact row count of a large table (estimates are good enough) so I'm not
sure I'd be willing to pay a price to have it.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.