Re: IS NOT DISTINCT FROM + Indexing

On Jul 22, 2014, at 12:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "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.

Well that definitely answers "how hard would it be." - before embarking on something laborious (as even just indexing is nontrivial), I think it would be good to figure out how people are using IS [NOT] DISTINCT FROM and if there is interest in having it be indexable, let alone used in a JOIN optimization. It could become a handy tool to simplify the SQL in queries that are returning a lot of NULL / NOT NULL data mixed together.

Jonathan