X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4D036D1B4F6 for ; Thu, 2 Oct 2003 19:52:13 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 67477-02 for ; Thu, 2 Oct 2003 16:51:28 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 1182CD1B4E1 for ; Thu, 2 Oct 2003 16:51:26 -0300 (ADT) Received: (qmail 19079 invoked by uid 500); 2 Oct 2003 19:58:43 -0000 Date: Thu, 2 Oct 2003 14:58:43 -0500 From: Bruno Wolff III To: Dror Matalon Cc: pgsql-performance@postgresql.org Subject: Re: count(*) slow on large tables Message-ID: <20031002195843.GA19021@wolff.to> Mail-Followup-To: Dror Matalon , pgsql-performance@postgresql.org References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <20031002194645.GA87525@rlx11.zapatec.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20031002194645.GA87525@rlx11.zapatec.com> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/63 X-Sequence-Number: 3811 On Thu, Oct 02, 2003 at 12:46:45 -0700, Dror Matalon wrote: Please keep replies copied to the list. > When would it happen that a tuple be invisible to the current > transaction? Are we talking about permissions? They could be tuples that were changed by a transaction that hasn't committed or in the case of serializable isolation, a transaction that committed after the current transaction started. > > On Thu, Oct 02, 2003 at 02:39:05PM -0500, Bruno Wolff III wrote: > > On Thu, Oct 02, 2003 at 12:15:47 -0700, > > Dror Matalon wrote: > > > Hi, > > > > > > I have a somewhat large table, 3 million rows, 1 Gig on disk, and growing. Doing a > > > count(*) takes around 40 seconds. > > > > > > Looks like the count(*) fetches the table from disk and goes through it. > > > Made me wonder, why the optimizer doesn't just choose the smallest index > > > which in my case is around 60 Megs and goes through it, which it could > > > do in a fraction of the time. > > > > Because it can't tell from the index if a tuple is visible to the current > > transaction and would still have to hit the table to check this. So that > > performance would be a lot worse instead of better. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > -- > Dror Matalon, President > Zapatec Inc > 1700 MLK Way > Berkeley, CA 94709 > http://www.zapatec.com