Re: IS NOT DISTINCT FROM + Indexing

"Jonathan S. Katz" <jonathan(dot)katz(at)excoventures(dot)com> writes:
> On Jul 21, 2014, at 9:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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".

> 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?

We hacked in IS [NOT] NULL as a potentially indexable construct, but the
key thing that made that possible without major redesign is that IS [NOT]
NULL is datatype independent, so there's no need to identify any
particular underlying operator or opclass. I'm not sure what we'd do to
handle IS [NOT] DISTINCT FROM, but that particular approach ain't gonna
cut it.

Another point is that people are unlikely to be satisfied with planner
optimization for IS NOT DISTINCT FROM that doesn't support it as a join
clause (i.e., tab1.col1 IS NOT DISTINCT FROM tab2.col2); which is an issue
that doesn't arise for IS [NOT] NULL, as it has only one argument. So
that brings you into not just indexability but hashing and merging
support. I hasten to say that that doesn't necessarily have to happen
in a version-zero patch; but trying to make IS NOT DISTINCT FROM into
a first-class construct is a big project.

regards, tom lane