Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 950FB9FB5D8 for ; Thu, 22 Mar 2007 10:39:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 96427-01 for ; Thu, 22 Mar 2007 10:39:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 20C6A9FBBD5 for ; Thu, 22 Mar 2007 10:39:19 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id x4so1529273nfb for ; Thu, 22 Mar 2007 06:39:18 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=oAPyBpqNGD+uDV2JWd8u2OewG0SYbrywoOBTmN22hUOdfY1VJKKJ5pW7pW6ae5/Z3dr6KfDAMLDcwgvWmgte3GEsFBmpS2g2d2ZBv+1nXcKMPoB/c8w/bNf+YqCtGp1WM3OVXW9dStPpVwroj2zPKvuFGkuF8x6TNUcecjMBa/o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nSMXj28xk1vLV0sUpP1w+KUpkkgCIdRnD/m/BGa9bwiOiiE+6Iii3h3S4X4pGM3PMFwGjzGQ3MYOinGGSlOMu7TCAkJpBkhBFwUk625Y5uI5qcblsC/KPDeQARlgmdqVjkNIHm8DTbYdm4jh8kLjgPH0xBO7jesvDGVNaIJCs3M= Received: by 10.78.164.9 with SMTP id m9mr944426hue.1174570758160; Thu, 22 Mar 2007 06:39:18 -0700 (PDT) Received: by 10.78.35.18 with HTTP; Thu, 22 Mar 2007 06:39:18 -0700 (PDT) Message-ID: Date: Thu, 22 Mar 2007 09:39:18 -0400 From: "Merlin Moncure" To: "Andreas Tille" Subject: Re: Performance of count(*) Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/317 X-Sequence-Number: 23722 On 3/22/07, Andreas Tille wrote: > I just try to find out why a simple count(*) might last that long. > At first I tried explain, which rather quickly knows how many rows > to check, but the final count is two orders of magnitude slower. You can get the approximate count by selecting reltuples from pg_class. It is valid as of last analyze. As others suggest select count(*) from table is very special case which non-mvcc databases can optimize for. There are many reasons why this is the case and why it explains nothing about the relative performance of the two databases. This is probably #1 most frequenctly asked question to -performance...there is a wealth of information in the archives. merlin