Re: Index not used with IS NULL

Dima Tkach <dmitry(at)openratings(dot)com> writes:
> Tom, as you said in your message "we do index nulls" - why do you index
> them, if there is no way to use those index values? :-)

So that an indexscan can be a substitute for seqscan + sort.

Also, in a multi-column index you must be prepared to index nulls,
or you won't correctly answer questions that look at only some of the
columns.

Index types that don't support ordered scans don't have to store nulls
(at least in their first column) and indeed rtree and gist do not. I
forget whether hash does.

> A row in the table is a tree node. A node can have one parent, ot no
> parent at all.

You're better off making the root node link to itself (compare handling
of /.. in a Unix filesystem). NULL parent link does not mean "has no
parent", it means "parent is unknown".

regards, tom lane