Re: IS NOT DISTINCT FROM + Indexing

On Jul 21, 2014, at 9:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Jonathan S. Katz" <jonathan(dot)katz(at)excoventures(dot)com> writes:
>> I'm curious if there is a reason why "IS NOT DISTINCT FROM" is not an
>> indexable operation in a B-tree index,
>
> The short reason why not is that it's not an operator (where "operator"
> is defined as "something with a pg_operator entry"), and all our indexing
> infrastructure is built around the notion that indexable clauses are of
> the form "indexed_column indexable_operator comparison_value".
>
> You could certainly imagine ways to fix that, but nobody's put in the
> probably-nontrivial effort required to do so. The btree code itself
> would likely be the easiest part to fix, as it sort of thinks nulls
> are real values already.

What got me thinking this initially problem is that I know "IS NULL" is indexable and I was unsure of how adding "IS NOT DISTINCT FROM" would be too different from that - of course, this is from my perspective from primarily operating on the surface. It sounds like the IS NULL work is in the btree code?

Even if it is trivial, it would be tough for me personally to hack on without some hand-holding. I did want to ask about it because it can be useful in simplifying some queries when you have do deal with NULLs (though in reality I tend to use IS DISTINCT FROM much more, though in things like triggers) and would be useful with exclusion constraints (though with those it sounds like it would have to be an operator?).

If it is a small project someone is interested, I would be happy to contribute by testing.

Jonathan