Re: Performance of count(*)

From: ismo.tuononen@solenovo.fi
Subject: Re: Performance of count(*)
Date: ,
Msg-id: Pine.LNX.4.64.0703221313560.1311@ismoli.solenovo.jns
(view: Whole thread, Raw)
In response to: Performance of count(*)  (Andreas Tille)
List: pgsql-performance

Tree view

Performance of count(*)  (Andreas Tille, )
 Re: Performance of count(*)  (Andreas Kostyrka, )
  Re: Performance of count(*)  (Andreas Tille, )
   Re: Performance of count(*)  (Andreas Kostyrka, )
    Re: Performance of count(*)  (Michael Fuhr, )
    Re: Performance of count(*)  (Carlos Moreno, )
   Re: Performance of count(*)  ("Luke Lonergan", )
 Re: Performance of count(*)  (Albert Cervera Areny, )
  Re: Performance of count(*)  (, )
   Re: Performance of count(*)  (Bill Moran, )
   Re: Performance of count(*)  (Mario Weilguni, )
   Re: Performance of count(*)  (Michael Stone, )
    Re: Performance of count(*)  (, )
    Re: Performance of count(*)  ("Craig A. James", )
     Re: Performance of count(*)  (Tino Wildenhain, )
      Re: Performance of count(*)  ("Craig A. James", )
       Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  ("Merlin Moncure", )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
          Re: Performance of count(*)  (Michael Stone, )
         Re: Performance of count(*)  (Tino Wildenhain, )
          Re: Performance of count(*)  (Michael Stone, )
           Re: Performance of count(*)  (Tino Wildenhain, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tino Wildenhain, )
       Re: Performance of count(*)  (Steve Atkins, )
        Re: Performance of count(*)  ("Craig A. James", )
         Re: Performance of count(*)  (Tom Lane, )
          Re: Performance of count(*)  ("Craig A. James", )
           Re: Performance of count(*)  (Tom Lane, )
        Re: Performance of count(*)  (Guido Neitzer, )
         Re: Performance of count(*)  (Steve Atkins, )
     Re: Performance of count(*)  (Brian Hurt, )
      Re: Performance of count(*)  ("Craig A. James", )
 Re: Performance of count(*)  (, )
 Re: Performance of count(*)  ("Merlin Moncure", )
  Re: Performance of count(*)  ("Jonah H. Harris", )
   Re: Performance of count(*)  (Mario Weilguni, )
    Re: Performance of count(*)  (Andreas Kostyrka, )
     Re: Performance of count(*)  (Mario Weilguni, )
  Re: Performance of count(*)  (Michael Stone, )

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
>


pgsql-performance by date:

From: Michael Stone
Date:
Subject: Re: Parallel Vacuum
From: "Craig A. James"
Date:
Subject: Re: Performance of count(*)