On Wed, 2005-03-09 at 23:53 +0100, Thomas Mueller wrote: > Geo Carncross wrote: > > On Wed, 2005-03-09 at 23:06 +0100, Thomas Mueller wrote: > >> Geo Carncross wrote: > >>> On Wed, 2005-03-09 at 19:10 +0100, Thomas Mueller wrote: > >>> > >>>> No index will ever solve the problem for PostgreSQL. COUNT() > >>>> can't use any index, because indices know nothing about > >>>> transactions. That is why every record that is counted using > >>>> the index has to be checked to see if it's involved in a > >>>> transaction (otherwise count() could return too high or low > >>>> numbers!) - then it's cheaper to do a full table scan and that > >>>> is what PostgreSQL does. > >>> > >>> Is EXPLAIN really wrong here? I see PG generating the same query > >>> plan for COUNT(*) as it appears to for every aggregate- and in > >>> each case it says: > >>> > >>> Aggregate (cost=123.57..123.57 rows=1 width=4) -> Index Scan > >>> using ... > >> > >> No I was wrong here I'm sorry. What I said is true for COUNT() > >> without restricting WHERE. > > > > Well, I would expect aggregates to walk the entire table > > Really? I wouldn't expect that a COUNT() has to walk the table. I > would expect it to walk the leaves of the b-tree of my index and that's it.
That said, I would hope COUNT(*) could be treated specially, but as we all know, hoping doesn't make it happen. -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
