pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Robins Tharakan <tharakan@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: count * performance issue
Date: Mon, 10 Mar 2008 20:11:27 -0700
Message-ID: <dcc563d10803102011i1749c2ccy9df76c99a18b060b@mail.gmail.com> (raw)
In-Reply-To: <36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com>
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<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>
	<Pine.GSO.4.64.0803101853090.25041@westnet.com>
	<36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com>

On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <tharakan@gmail.com> 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.



view thread (62+ messages)  latest in thread

Message-ID: <dcc563d10803102011i1749c2ccy9df76c99a18b060b@mail.gmail.com>
Permalink:  ../dcc563d10803102011i1749c2ccy9df76c99a18b060b@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10803102011i1749c2ccy9df76c99a18b060b@mail.gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, tharakan@gmail.com
  Subject: Re: count * performance issue
  In-Reply-To: <dcc563d10803102011i1749c2ccy9df76c99a18b060b@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox