Re:Why Use Postgres
by reactor on Monday April 30, 2012 @10:04PM
(#3912636)
There is no fiasco in count(*), it is THE general behavior of any MVCC database. Try the same with MySQL InnoDB, you get the same result (or worse).
MySQL MyISAM DB gets the result fast because it is non-trasactional.
To get approximate count very quickly on Pg, you can use
-- select reltuples from pg_class where relname='<table_name>';
[ Parent
| on HN ]
Indeed, in the benchmarks I have seen PostgreSQL is faster then InnoDB at running count(*) over entire tables but slower when a indexable WHERE clause was added due to the lack of index only scans in PostgreSQL. This may change in 9.2.