Re: Why is NULL not indexable?

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Tue, Jun 26, 2001 at 11:02:41AM -0400, Tom Lane wrote:
>> I believe the main problem is that IS NULL and IS NOT NULL are not
>> operators (they don't have pg_operator entries), and all of the planning
>> and indexscan execution machinery is designed around operators. Binary
>> operators, at that.

> Ok, I can see at least part of the problem now. You could make two operators
> 'is' and 'isnot' and have them equivalent to '=' and '!=' except in the case
> of nulls. The index code is doing something like this anyway already.

Btree does that, but it might not work at all for non-btree indexes.
Besides, you'd need a pair of such operators for every indexable
datatype. I'd prefer to see the notion of IS (NOT) NULL directly
expressed in some fashion in the ScanKey representation. Phony
operators corresponding to the (existing) functions nullvalue and
nonnullvalue might work.

> Is there any documentation describing how this all works?

http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/xindex.html

> All that stuff relating
> to strategies (whatever they are) seems like it could do with some #defines
> indicating what the numbers mean.

The generic strategy stuff doesn't *know* what the numbers mean, since
they are index access method specific. See, eg, for btree
src/include/access/nbtree.h:

/*
* Operator strategy numbers -- ordering of these is <, <=, =, >=, >
*/

#define BTLessStrategyNumber 1
#define BTLessEqualStrategyNumber 2
#define BTEqualStrategyNumber 3
#define BTGreaterEqualStrategyNumber 4
#define BTGreaterStrategyNumber 5
#define BTMaxStrategyNumber 5

> As a side note, the partial index stuff seems to be still perfectly fine in
> the indexing code, you say it's the planner that doesn't handle it right?

The planner code is still there. Someone once ripped out the WHERE
clause from CREATE INDEX in the grammar, for reasons undocumented and
now forgotten. It's hard to guess what might need to be fixed after
adding that back --- surely all that code is now suffering bit-rot to
some extent.

regards, tom lane