Skip site navigation (1) Skip section navigation (2)

Re: Performance of count(*)

From: Andreas Kostyrka <andreas(at)kostyrka(dot)org>
To: Andreas Tille <tillea(at)rki(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance of count(*)
Date: 2007-03-22 11:10:47
Message-ID: 20070322111044.GC4439@andi-lap.la.revver.com (view raw or flat)
Thread:
Lists: pgsql-performance
* 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

In response to

Responses

pgsql-performance by date

Next:From: ismo.tuononenDate: 2007-03-22 11:18:16
Subject: Re: Performance of count(*)
Previous:From: Albert Cervera ArenyDate: 2007-03-22 11:08:19
Subject: Re: Performance of count(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group