Re: Null values in indexes

Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> Tom Lane wrote:
>> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> How hard would it be to _not_ include nulls in indexes
> as they are not used anyway.
>>
>> Seems to me that would be a step backwards.

> It would cause multi-key indexes beeing unusable for partial
> key lookup. Imagine you have a key over (a, b, c) and query
> with WHERE a = 1 AND b = 2. This query cannot use the index
> if a NULL value in c would cause the index entry to be
> suppressed.

Urgh ... that means GiST indexing is actually broken, because GiST
currently handles multicolumns but not nulls. AFAIR the planner
will try to use partial qualification on any multicolumn index...
it had better avoid doing so for non-null-capable AMs.

Alternatively, we could fix GiST to support nulls. Oleg, Teodor:
how far away might that be?

regards, tom lane