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. Thomas -- http://www.tmueller.com for pgp key (95702B3B)
