Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6956C2E2D3E for ; Tue, 11 Mar 2008 00:02:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 53585-04 for ; Tue, 11 Mar 2008 00:02:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca [206.248.154.182]) by postgresql.org (Postfix) with ESMTP id 33F0F2E2D22 for ; Tue, 11 Mar 2008 00:02:34 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApsEAAaT1UfO+Igp/2dsb2JhbACCcKhHgQc X-IronPort-AV: E=Sophos;i="4.25,477,1199682000"; d="scan'208,217";a="15820647" Received: from smtp.pppoe.ca ([65.39.196.238]) by ironport2-out.pppoe.ca with ESMTP; 10 Mar 2008 23:02:32 -0400 Received: from [206.248.136.41] ([206.248.136.41]) by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id RGN76131; Mon, 10 Mar 2008 23:02:31 -0400 Message-ID: <47D5F61F.4080305@mark.mielke.cc> Date: Mon, 10 Mar 2008 23:01:51 -0400 From: Mark Mielke User-Agent: Thunderbird 2.0.0.12 (X11/20080226) MIME-Version: 1.0 To: Robins Tharakan CC: pgsql-performance@postgresql.org 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> In-Reply-To: <36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com> Content-Type: multipart/alternative; boundary="------------060803010706060307090803" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/219 X-Sequence-Number: 29173 This is a multi-part message in MIME format. --------------060803010706060307090803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Robins Tharakan wrote: > Hi, > > I have been reading this conversation for a few days now and I just > wanted to ask this. From the release notes, one of the new additions > in 8.3 is (Allow col IS NULL to use an index (Teodor)). > > Sorry, if I am missing something here, but shouldn't something like > this allow us to get a (fast) accurate count ? > > SELECT COUNT(*) from table WHERE indexed_field IS NULL > + > SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For PostgreSQL: You still don't know whether the row is visible until you check the row. That it's NULL or NOT NULL does not influence this truth. Cheers, mark -- Mark Mielke --------------060803010706060307090803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Robins Tharakan wrote:
Hi,

I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)).

Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ?

SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL

For PostgreSQL: You still don't know whether the row is visible until you check the row. That it's NULL or NOT NULL does not influence this truth.

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>
--------------060803010706060307090803--