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. I guess the following happens with your query: in my example it returns only 600 of about 80000 records, that's a selectivity of 0.75%. So it's cheaper to do a index scan and check the records afterwards than to do a full table scan. But if I'm not completely wrong the 600 records have to be read, it's not enough to read the index (because of missing transaction information). > All these counts can be translated into SUM() - will it act different > (even though the query plan is the same) when using SUM? No, the query plan is what will be executed if it's the same it shouldn't make a difference - at least for you :) On two machines two queries can lead to different query plans depending on the expected costs (IO p.e.). > Does the transaction isolation level have any useful effect? I don't think so. For an application like dbmail MVCC should be perfect. Lowering the isolation level shouldn't improve anything. Thomas -- http://www.tmueller.com for pgp key (95702B3B)
