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

Re: Performance of count(*)

From: ismo(dot)tuononen(at)solenovo(dot)fi
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:18:16
Message-ID: Pine.LNX.4.64.0703221313560.1311@ismoli.solenovo.jns (view raw or flat)
Thread:
Lists: pgsql-performance
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
> 

In response to

pgsql-performance by date

Next:From: ismo.tuononenDate: 2007-03-22 11:30:35
Subject: Re: Performance of count(*)
Previous:From: Andreas KostyrkaDate: 2007-03-22 11:10:47
Subject: Re: Performance of count(*)

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