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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | ismo.tuononen | 2007-03-22 11:30:35 | Re: Performance of count(*) |
Previous Message | Andreas Kostyrka | 2007-03-22 11:10:47 | Re: Performance of count(*) |