X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1B2EB8B9D02 for ; Tue, 1 Feb 2005 13:01:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70155-10 for ; Tue, 1 Feb 2005 13:01:03 +0000 (GMT) Received: from smtp.pspl.co.in (smtp.persistent.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 1735C8B9E22 for ; Tue, 1 Feb 2005 13:01:03 +0000 (GMT) Received: from ps0499.persistent.co.in (PS0499.intranet.pspl.co.in [192.168.10.204]) (authenticated bits=0) by smtp.pspl.co.in (8.12.9/8.12.9) with ESMTP id j11D9JQ2010121; Tue, 1 Feb 2005 18:39:19 +0530 From: Shridhar Daithankar To: Andrew Mayo Subject: Re: Performance of count(*) on large tables vs SQL Server Date: Tue, 1 Feb 2005 18:32:56 +0530 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org References: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com> In-Reply-To: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200502011832.56333.ghodechhap@ghodechhap.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/15 X-Sequence-Number: 10344 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