Re: Index not used with IS NULL

Tom Lane wrote:

>Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>I don't see anything dangerous about it --- except perhaps to
>>>readability and mantainability of the code. The problem is that IS NULL
>>>doesn't fit into the operator-and-opclass model of what indexes can do.
>>>If you can find a solution to that problem that's not a complete kluge,
>>>I'm all ears.
>>>
>>>
>
>
>
>>Well... At first glance, it seems that what needs to be done is:
>>
>>
>
>
>
>>- add a special case in is_indexable_operator() to return true for IS_NULL
>>
>>
>
>And is_indexable_operator() will know that this is safe how? Or do you
>plan to fix the other three index types to support NULLs too?
>
Well, yeah... Generally, I think, the best thing to do is to fix them
all - if it can handle '=', there is no reason it cannot be made to
handle 'is null' as well...
I haven't seen much of the code for rtree and hash, but I doubt it (null
handling) is going to be that much different from btree...

But if we do not want to fix them all, just btree, you are right -
indexable_operator() needs to know about that... In that case, we'd
either need to add a param to it to pass the index info, or leave it
alone, and just handle the is null case separately, at the higher level...
Either way, we'd probably want to add something like idxisstrict to
pg_index - checking for btree explicitly would look like a kludge even
to me :-)

>
>
>
>>- modify _bt_checkkeys () to return isNull from inside if
>>(key->sk_flags & SK_ISNULL) clause instead of just false.
>>- remove sk_flags & SK_ISNULL checks from _bt_orderkeys
>>
>>
>
>IIRC, SK_ISNULL marks that the value being compared against is null
>--- not that the scan operator is ISNULL. An approach as above would
>cause "WHERE x = something" indexscans to start returning nulls if the
>"something" is null, no? You need a representation that preserves the
>difference between "x = NULL" and "x IS NULL". The ScanKey structure
>can't do this at the moment, mainly because it assumes that the scan
>operator *is* an operator. Which IS NULL is not.
>
>
This can be resolved by just putting null (0) as the operator in the
ScanKey to denote is null.
I know, this looks ugly, but the ugliness is due to 'is null' not being
an operator in the first place... :-)

And another possibility is to create isnull () operator... but that
would have to wait until postgres allows functions with unknown argument
types (this can be done - just always pass pointers, and let the
function figure out what it wants to do with them)...
Otherwise, as you said earlier, creating isnull() for every possible
type would be quite a job.... and besides, there are also user-defined
types too...

Dima