Re: Performance of count(*)

explain is just "quessing" how many rows are in table. sometimes quess is
right, sometimes just an estimate.

sailabdb=# explain SELECT count(*) from sl_tuote;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (cost=10187.10..10187.11 rows=1 width=0)
-> Seq Scan on sl_tuote (cost=0.00..9806.08 rows=152408 width=0)
(2 rows)

sailabdb=# SELECT count(*) from sl_tuote;
count
-------
62073
(1 row)

so in that case explain estimates that sl_tuote table have 152408 rows, but
there are only 62073 rows.

after analyze estimates are better:

sailabdb=# vacuum analyze sl_tuote;
VACUUM
sailabdb=# explain SELECT count(*) from sl_tuote;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (cost=9057.91..9057.92 rows=1 width=0)
-> Seq Scan on sl_tuote (cost=0.00..8902.73 rows=62073 width=0)
(2 rows)

you can't never trust that estimate, you must always count it!

Ismo

On Thu, 22 Mar 2007, Andreas Tille wrote:

> Hi,
>
> I just try to find out why a simple count(*) might last that long.
> At first I tried explain, which rather quickly knows how many rows
> to check, but the final count is two orders of magnitude slower.
>
> My MS_SQL server using colleague can't believe that.
>
> $ psql InfluenzaWeb -c 'explain SELECT count(*) from agiraw ;'
> QUERY PLAN
> -----------------------------------------------------------------------
> Aggregate (cost=196969.77..196969.77 rows=1 width=0)
> -> Seq Scan on agiraw (cost=0.00..185197.41 rows=4708941 width=0)
> (2 rows)
>
> real 0m0.066s
> user 0m0.024s
> sys 0m0.008s
>
> $ psql InfluenzaWeb -c 'SELECT count(*) from agiraw ;'
> count ---------
> 4708941
> (1 row)
>
> real 0m4.474s
> user 0m0.036s
> sys 0m0.004s
>
>
> Any explanation?
>
> Kind regards
>
> Andreas.
>
> --
> http://fam-tille.de
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>