Re: COUNT and Performance ...

On Sun, 2003-02-02 at 13:04, Tom Lane wrote:
> I think your test case is small enough that the whole table is resident
> in memory, so this measurement only accounts for CPU time per tuple and
> not any I/O. Given the small size of pgstattuple's per-tuple loop, the
> speed differential is not too surprising --- but it won't scale up to
> larger tables.

Good observation.

When the entire table is in cache, pgstattuple about 4 times faster than
count(*) on my machine. When the table is too large to fit into cache,
the performance difference drops to 8% in favour of pgstattuple:

nconway=# select count(*) from big_table;
count
---------
8388612
(1 row)

Time: 26769.99 ms
nconway=# SELECT tuple_count FROM pgstattuple('big_table');
tuple_count
-------------
8388612
(1 row)

Time: 24658.87 ms

Cheers,

Neil
--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC