On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <tharakan@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
