On Fri, Oct 28, 2016 at 1:20 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Fri, Oct 28, 2016 at 1:17 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Scott Marlowe <scott.marlowe@xxxxxxxxx> writes: >>> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen <krc@xxxxxxxx> wrote: >>>> I was wondering if there is a way to hint that two columns in two different >>>> tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if >>>> table_a.key = 'test' THEN table_b.key = 'test' . >>>> >>>> The equals operator already does this but it does not handle NULLS very well >>>> (or not at all). And it seems IS NOT DISTINCT FROM is not indexable and >>>> doesn't establish the same inference rules as equals. >> >>> The whole idea behing Postgres' query planner is that you don't have >>> to use any hints. Late model versions of postgres handle nulls fine, >>> but nulls are never "equal" to anything else. I.e. where xxx is null >>> works with indexes. Where x=y does not, since null <> null. >> >> The bigger picture here is that if you've designed a data representation >> that requires that a null be considered "equal to" another null, you're >> really going to be fighting against the basic semantics of SQL. You'd >> be best off to rethink the representation. We've not seen enough info >> about your requirements to suggest just how, though. > > Well, ISTM OP is simply asking, "why can't IS [NOT] DISTINCT FROM be > optimized"? It ought to be, at least in some cases. Internally > indexes handle nulls so you should be able to implement them to > satisfy those kinds of scans. I guess that's an easy thing to say > though. hm. ISTM "a IS NOT DISTINCT FROM b"...could be rewritten into ((a IS NULL AND b IS NULL) OR (a = b)) ... which is an indexable expression. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
