Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9859E2E01E3 for ; Tue, 11 Mar 2008 00:11:35 -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 61676-03 for ; Tue, 11 Mar 2008 00:11:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.178]) by postgresql.org (Postfix) with ESMTP id 7DA792E016D for ; Tue, 11 Mar 2008 00:11:29 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so2646651waf.5 for ; Mon, 10 Mar 2008 20:11:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; 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; bh=ewTJlx5QtsVXx7QmgWGdWFhEsKNO21JT3UVoF3zA0o4=; b=YGAbCP0PvpHcj+kfXBREJwDez5DO/hIGtVmjoVTwz18A/sTp7SK2IF6C4Y7fMIaN27DyXV+YU9ElCiRe0A1nAk4NFCSqpaNrIFUJk3qqaZRXcIOOjLyc2m0aUcbqTiOkxj/kppiVxkd48PIVZ5CqKu2btpb15FhxlZG5qOtHyDw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=mK4eLT3IFRFuXsXhLbqrNwGcnuh+h+H3kAB2JDO3RC7r6mCZamknxmkuUnfAOwcRxjQCQvCF90URdfhtCISD3Fu/5T1KqLhh3f5SeKZYbSnCoHjBRYMt3/ZbFshkT9rm3ABDtToUB/xfbstJTrmQaY7hDCApq5l/UOpEi91lBCY= Received: by 10.114.194.1 with SMTP id r1mr4197778waf.40.1205205088762; Mon, 10 Mar 2008 20:11:28 -0700 (PDT) Received: by 10.114.133.6 with HTTP; Mon, 10 Mar 2008 20:11:27 -0700 (PDT) Message-ID: Date: Mon, 10 Mar 2008 20:11:27 -0700 From: "Scott Marlowe" To: "Robins Tharakan" Subject: Re: count * performance issue Cc: pgsql-performance@postgresql.org In-Reply-To: <36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/221 X-Sequence-Number: 29175 On Mon, Mar 10, 2008 at 7:57 PM, 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 It really depends on the distribution of the null / not nulls in the table. If it's 50/50 there's no advantage to using the index, as you still have to check visibility info in the table itself. OTOH, if NULL (or converserly not null) are rare, then yes, the index can help. I.e. if 1% of the tuples are null, the select count(*) from table where field is null can use the index efficiently.