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 ... All these counts can be translated into SUM() - will it act different (even though the query plan is the same) when using SUM? Does the transaction isolation level have any useful effect? [SQLite has only minor speed difference when using SUM because of NULL check] > > I guess your suggestion to undo the database abstraction has some merit, > > but we need to think about this some more before we go that way: > > > > Most code *is* shared between drivers. > > Drivers should be able to use backend optimized queries. > > Hm yes. db.c contains 105 db_query() calls, I'm sure we don't want that > in every backend. So we need something like 'function overloading' I > guess? In that case pgsql.c would have to overload db_getmailbox() ? Is > that possible in C ? > Maybe the granularity has to be reduced - one SQL query one function. This actually isn't _too_ hard, some possibilities: 1. have db_getmailbox do a: int (*x)(mailbox_t*) = (int(*)(mailbox_t*))dlsym(dlopen(0,RTLD_LAZY), "dbspecific_getmailbox") if (x != NULL) return x(mb); 2. use indirects to reach db_getmailbox int (*db_getmailbox)(mailbox_t *x) = dbgeneric_getmailbox; (and allow so to alter db_getmailbox) 3. define db_getmailbox as a "weak symbol" and move all those db_ calls into a separate .so #3 looks good because it makes the linker do most of the work. This wouldn't give any gains on x86 (afaik), but systems that actually have non-hackish PIC should work great. -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
