Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F30422E01C9 for ; Fri, 7 Mar 2008 19:51:50 -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 59719-02 for ; Fri, 7 Mar 2008 19:51:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp3.clear.net.nz (smtp3.clear.net.nz [203.97.33.64]) by postgresql.org (Postfix) with ESMTP id BA32F2E01E7 for ; Fri, 7 Mar 2008 19:51:47 -0400 (AST) Received: from zmori.markir.net (121-73-160-31.dsl.telstraclear.net [121.73.160.31]) by smtp3.clear.net.nz (CLEAR Net Mail) with ESMTP id <0JXD00ALGWXKOO30@smtp3.clear.net.nz> for pgsql-performance@postgresql.org; Sat, 08 Mar 2008 12:51:22 +1300 (NZDT) Date: Sat, 08 Mar 2008 12:51:19 +1300 From: Mark Kirkwood Subject: Re: count * performance issue In-reply-to: <47D0AFC1.5070305@emolecules.com> To: Craig James Cc: Tom Lane , pgsql-performance@postgresql.org Message-id: <47D1D4F7.9040502@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <20080306060829.GA21084@a-kretschmer.de> <47CF8EA6.8070503@mark.mielke.cc> <20080306063643.GB21084@a-kretschmer.de> <3E9C3A51-C28E-4B75-9739-E344DAC72880@fastcrypt.com> <47D00DB2.7040007@emolecules.com> <18451.1204824562@sss.pgh.pa.us> <47D0AFC1.5070305@emolecules.com> User-Agent: Thunderbird 2.0.0.9 (X11/20071203) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/169 X-Sequence-Number: 29123 Craig James wrote: > Tom Lane wrote: >> Craig James writes: >>> Count() on Oracle and MySQL is almost instantaneous, even for very >>> large tables. So why can't Postgres do what they do? >> >> AFAIK the above claim is false for Oracle. They have the same >> transactional issues we do. > > My experience doesn't match this claim. When I ported my application > from Oracle to Postgres, this was the single biggest performance > problem. count() in Oracle was always very fast. We're not talking > about a 20% or 50% difference, we're talking about a small fraction of > a second (Oracle) versus a minute (Postgres) -- something like two or > three orders of magnitude. > To convince yourself do this in Oracle: EXPLAIN PLAN FOR SELECT count(*) FROM table_without_any_indexes and you will see a full table scan. If you add (suitable) indexes you'll see something like an index full fast scan. In fact you can make count(*) *very* slow indeed in Oracle, by having an older session try to count a table that a newer session is modifying and committing to. The older session's data for the count is reconstructed from the rollback segments - which is very expensive. regards Mark