Thread: Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)

> 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

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

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

> > 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