From: Shridhar Daithankar <ghodechhap@ghodechhap.net>
To: Andrew Mayo <ajmayo@kohuconsulting.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*) on large tables vs SQL Server
Date: Tue, 1 Feb 2005 18:32:56 +0530
Message-ID: <200502011832.56333.ghodechhap@ghodechhap.net> (raw)
In-Reply-To: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com>
References: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com>
On Tuesday 01 Feb 2005 6:11 pm, Andrew Mayo wrote:
> PG, on the other hand, appears to do a full table scan
> to answer this question, taking nearly 4 seconds to
> process the query.
>
> Doing an ANALYZE on the table and also VACUUM did not
> seem to affect this.
>
> Can PG find a table's row count more efficiently?.
> This is not an unusual practice in commercial
> applications which assume that count(*) with no WHERE
> clause will be a cheap query - and use it to test if
> a table is empty, for instance. (because for
> Oracle/Sybase/SQL Server, count(*) is cheap).
First of all, such an assumption is no good. It should hit concurrency under
heavy load but I know people do use it.
For the specific question, after a vacuum analyze, you can use
select reltuples from pg_class where relname='Foo';
Remember, you will get different results between 'analyze' and 'vacuum
analyze', since later actually visit every page in the table and hence is
expected to be more accurate.
> (sure, I appreciate there are other ways of doing
> this, but I am curious about the way PG works here).
Answer is MVCC and PG's inability use index alone. This has been a FAQ for a
loong time.. Furthermore PG has custom aggregates to complicate the matter..
Most of the pg developers/users think that unqualified select count(*) is of
no use. You can search the archives for more details..
HTH
Shridhar
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: ghodechhap@ghodechhap.net, ajmayo@kohuconsulting.com
Subject: Re: Performance of count(*) on large tables vs SQL Server
In-Reply-To: <200502011832.56333.ghodechhap@ghodechhap.net>
* 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