This should definitely be a FAQ. The semantics of MVCC (multi-version concurrency control) means that you can't just store a number somewhere in the header of the table like some other database systems do.
Try a count(*) on Oracle and you will see similar behavior. They use MVCC also. > -----Original Message----- > From: Mark Harrison [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 15, 2003 11:00 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] constant time count(*) ? > > > We're looking into moving some data from mysql to postgresql, > and notice that count(*) does not seem to be a constant-time > function as it seems to be in mysql. > > planb=# explain select count(*) from assets; > QUERY PLAN > ---------------------------------------------------------------- > Aggregate (cost=22.50..22.50 rows=1 width=0) > -> Seq Scan on assets (cost=0.00..20.00 rows=1000 > width=0) (2 rows) > > Is there a way to optimize count(*) such that it does not > have to do a sequential scan? We use this on some big tables > and it is slowing down processing quite a lot. > > Thanks! > Mark > > -- > Mark Harrison > Pixar Animation Studios > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
