Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3E42C2E2EB3 for ; Thu, 6 Mar 2008 11:49:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 35614-03 for ; Thu, 6 Mar 2008 11:49:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id C9CE92E0206 for ; Thu, 6 Mar 2008 11:49:34 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m26FnXWm000039 for ; Thu, 6 Mar 2008 10:49:33 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m26FnXK9000036 for ; Thu, 6 Mar 2008 10:49:33 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Thu, 6 Mar 2008 10:49:33 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: pgsql-performance@postgresql.org Subject: Re: count * performance issue In-Reply-To: <20080306153639.GA19442@samfundet.no> Message-ID: References: <47CF8EA6.8070503@mark.mielke.cc> <20080306063643.GB21084@a-kretschmer.de> <3E9C3A51-C28E-4B75-9739-E344DAC72880@fastcrypt.com> <47D00DB2.7040007@emolecules.com> <20080306153639.GA19442@samfundet.no> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/126 X-Sequence-Number: 29080 On Thu, 6 Mar 2008, Steinar H. Gunderson wrote: > On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote: >> Count() on Oracle and MySQL is almost instantaneous, even for very large >> tables. So why can't Postgres do what they do? > > In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.) Exactly. There is a good discussion of this at http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ and I found the comments from Ken Jacobs were the most informative. In short, if you want any reasonable database integrity you have to use InnoDB with MySQL, and once you make that choice it has the same problem. You only get this accelerated significantly when using MyISAM, which can tell you an exact count of all the rows it hasn't corrupted yet. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD