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 6E1B58B9EC5 for ; Tue, 1 Feb 2005 13:19:46 +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 72593-06 for ; Tue, 1 Feb 2005 13:19:38 +0000 (GMT) Received: from smtpout03-04.mesa1.secureserver.net (smtpout03-01.mesa1.secureserver.net [64.202.165.71]) by svr1.postgresql.org (Postfix) with SMTP id E85A48B9EA0 for ; Tue, 1 Feb 2005 13:19:39 +0000 (GMT) Received: (qmail 5142 invoked from network); 1 Feb 2005 13:19:39 -0000 Received: from unknown (64.231.136.13) by smtpout03-04.mesa1.secureserver.net (64.202.165.74) with ESMTP; 01 Feb 2005 13:19:39 -0000 Date: Tue, 1 Feb 2005 08:22:50 -0500 From: Stef To: Shridhar Daithankar Cc: Andrew Mayo , pgsql-performance@postgresql.org Subject: Re: Performance of count(*) on large tables vs SQL Server Message-ID: <20050201132250.GF32625@survivor.hades> References: <20050201124143.43977.qmail@web206.biz.mail.re2.yahoo.com> <200502011832.56333.ghodechhap@ghodechhap.net> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="qM81t570OJUP5TU/" Content-Disposition: inline In-Reply-To: <200502011832.56333.ghodechhap@ghodechhap.net> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/16 X-Sequence-Number: 10345 --qM81t570OJUP5TU/ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hello Andrew, Everything that Shridhar says makes perfect sense, and, speaking from experience in dealing with this type of 'problem', everything you say does as=20 well. Such is life really :) I would not be at -all- surprised if Sybase and Oracle did query re-writing behind the scene's to send un-defined count's to a temporary table which holds the row count. For an example of such done in postgreSQL (using triggers and a custom procedure) look into the 'General Bits' newsletter. Specifically http://www.varlena.com/varlena/GeneralBits/49.php I know, giving a URL as an answer 'sucks', but, well, it simply repeats my experience. Triggers and Procedures. Regards Steph On Tue, Feb 01, 2005 at 06:32:56PM +0530, Shridhar Daithankar wrote: > 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). >=20 > First of all, such an assumption is no good. It should hit concurrency un= der=20 > heavy load but I know people do use it. >=20 > For the specific question, after a vacuum analyze, you can use=20 >=20 > select reltuples from pg_class where relname=3D'Foo'; >=20 > Remember, you will get different results between 'analyze' and 'vacuum=20 > analyze', since later actually visit every page in the table and hence is= =20 > expected to be more accurate. >=20 > > (sure, I appreciate there are other ways of doing > > this, but I am curious about the way PG works here). >=20 > Answer is MVCC and PG's inability use index alone. This has been a FAQ fo= r a=20 > loong time.. Furthermore PG has custom aggregates to complicate the matte= r.. >=20 > Most of the pg developers/users think that unqualified select count(*) is= of=20 > no use. You can search the archives for more details.. >=20 > HTH >=20 > Shridhar >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? >=20 > http://archives.postgresql.org >=20 --qM81t570OJUP5TU/ Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQFB/4KqjI9jiT2RxJQRAhw1AJ9XU9JWwKWJNjmXfXABlhbPi80g2gCfaA0v rZkYFoz9xMx1pQAzIBje1cU= =omgh -----END PGP SIGNATURE----- --qM81t570OJUP5TU/--