From: Andreas Kostyrka <andreas@kostyrka.org>
To: Andreas Tille <tillea@rki.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 12:10:47 +0100
Message-ID: <20070322111044.GC4439@andi-lap.la.revver.com> (raw)
In-Reply-To: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
References: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
* Andreas Tille <tillea@rki.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@kostyrka.org, tillea@rki.de
Subject: Re: Performance of count(*)
In-Reply-To: <20070322111044.GC4439@andi-lap.la.revver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox