Re: Performance of count(*)

* Andreas Tille <tillea(at)rki(dot)de> [070322 12:07]:
> 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.

Which version of PG?

The basic problem is, that explain knows quickly, because it has it's
statistics.

The select proper, OTOH, has to go through the whole table to make
sure which rows are valid for your transaction.

That's the reason why PG (check the newest releases, I seem to
remember that there has been some aggregate optimizations there), does
a SeqScan for select count(*) from table. btw, depending upon your
data, doing a select count(*) from table where user=X will use an
Index, but will still need to fetch the rows proper to validate them.

Andreas

>
> 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