On Jul 22, 2014, at 12:40 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "Jonathan S. Katz" <jonathan.k...@excoventures.com> writes: >> On Jul 21, 2014, at 9:51 PM, Tom Lane <t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
