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

Performance of count(*)

From: Andreas Tille <tillea(at)rki(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance of count(*)
Date: 2007-03-22 10:53:00
Message-ID: Pine.LNX.4.62.0703221149210.13747@wr-linux02 (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Albert Cervera ArenyDate: 2007-03-22 11:08:19
Subject: Re: Performance of count(*)
Previous:From: Michael Ben-NesDate: 2007-03-22 09:08:02
Subject: Lower Random Access Time vs RAID 0 / 1

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