Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B85042E00FF for ; Tue, 11 Mar 2008 11:31:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16702-04 for ; Tue, 11 Mar 2008 11:31:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 1D7982E00EE for ; Tue, 11 Mar 2008 11:31:25 -0300 (ADT) thread-index: AciDhKnE2WocaoBVTC+cmqyZhwEvlQ== Received: from [172.24.32.184] ([62.232.55.118]) by mail01.enterprisedb.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.3959); Tue, 11 Mar 2008 10:31:56 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133 Message-ID: <47D697B6.9070106@enterprisedb.com> Date: Tue, 11 Mar 2008 14:31:18 +0000 From: "Heikki Linnakangas" Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110) MIME-Version: 1.0 To: "Matthew" Cc: Subject: Re: count * performance issue References: <47D00DB2.7040007@emolecules.com> <18451.1204824562@sss.pgh.pa.us> <200803071727.36146.josh@agliodbs.com> <13345.1204940295@sss.pgh.pa.us> <87r6eia9tz.fsf@oxford.xeocode.com> <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com> <36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com> <20080311093537.a60e2e3d.wmoran@collaborativefusion.com> <47D690A2.2090509@wildenhain.de> In-Reply-To: Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 11 Mar 2008 14:31:56.0843 (UTC) FILETIME=[A8A5F7B0:01C88384] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/233 X-Sequence-Number: 29187 Matthew wrote: > No, actually I was referring to a race condition. So, you find the count > of rows with IS NULL, then someone changes a row, then you find the > count of rows with IS NOT NULL. Add the two together, and there may be > rows that were counted twice, or not at all. Not a problem if you use a serializable transaction, or if you do SELECT COUNT(*) from table WHERE indexed_field IS NULL UNION ALL SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL as one statement. However, this makes no sense whatsoever. As both index scans (assuming the planner even chooses an index scan for them, which seems highly unlikely) still have to visit each tuple in the heap. It's always going to be slower than a single "SELECT COUNT(*) FROM table" with a seq scan. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com