
> Yes, I think we index nulls.  What are partial indexes?
> 
A create index statement that accepts a where condition. All
rows that satisfy the where condition are indexed, others not.
This needs intelligence in the optimizer.

This was in postgresql code some time ago, but was removed
for some reason I don't remember.

Example: create index ax0 on a (id) where id is not null;

Andreas


Wed, 28 Jul 1999 10:00:28 +0200
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se
	lect)
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at> writes:
>> Yes, I think we index nulls.  What are partial indexes?
>> 
> A create index statement that accepts a where condition. All
> rows that satisfy the where condition are indexed, others not.
> This needs intelligence in the optimizer.

> This was in postgresql code some time ago, but was removed
> for some reason I don't remember.

It was?  There's still a ton of code in the optimizer to support it
(a big chunk of indxqual.c is for testing index predicates).

			regards, tom lane

Wed, 28 Jul 1999 10:16:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se
	lect)
> > This was in postgresql code some time ago, but was removed
> > for some reason I don't remember.
> It was?  There's still a ton of code in the optimizer to support it
> (a big chunk of indxqual.c is for testing index predicates).

There was talk of removing it, but it seemed to be a Bad Idea to do
so. The discussion even provoked a negative response from the Gods
themselves (in the voice of Paul Aoki) and led to the short
description of them in the docs.

They have definitely been neglected, but are a Good Thing and should
be rehabbed...

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 28 Jul 1999 14:51:17 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se
	lect)
The code for partial indices is still intact in RTREES, and there is
some
information about them in one of the Stonebraker papers.  If anyone is
intersted I will dig up my file and look for an exact reference.  

Bernie

Wed, 28 Jul 1999 15:02:41 +0000
Bernard Frankpitt <frankpit@pop.dn.net>
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple
 select)