Thread

1. [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL

   Marti Raudsepp <marti@juffo.org> — 2012-03-08T17:00:58Z

   Hi list,
   
   This patch enables a simple optimization in
   eval_const_expressions_mutator. If we know that one argument to
   DistinctExpr is NULL then we can optimize it to a NullTest, which can
   be an indexable expression.
   
   For example the query:
   EXPLAIN (costs off) SELECT * FROM foo WHERE j IS NOT DISTINCT FROM NULL;
   
   Old behavior:
   Seq Scan on foo
     Filter: (NOT (j IS DISTINCT FROM NULL::integer))
   
   New behavior:
   Index Scan using foo_j_idx on foo
     Index Cond: (j IS NULL)
   
   Regards,
   Marti
   

2. Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-08T17:35:36Z

   Marti Raudsepp <marti@juffo.org> writes:
   > This patch enables a simple optimization in
   > eval_const_expressions_mutator. If we know that one argument to
   > DistinctExpr is NULL then we can optimize it to a NullTest, which can
   > be an indexable expression.
   
   Uh ... how much do we care about that?  I can't say that I've heard many
   people complain about the fact that IS [NOT] DISTINCT FROM is poorly
   optimized -- which it is, in general, and this patch chips away at that
   only a tiny bit, not enough to make it recommendable.  If we really
   wanted to make that a first-class operation we would need far more work
   than this.  Plus I don't see why anyone would write the specific case
   "IS [NOT] DISTINCT FROM NULL" when they could write half as much.
   
   			regards, tom lane
   
   

3. Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL

   Marti Raudsepp <marti@juffo.org> — 2012-03-08T18:16:00Z

   On Thu, Mar 8, 2012 at 19:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > Uh ... how much do we care about that?  I can't say that I've heard many
   > people complain about the fact that IS [NOT] DISTINCT FROM is poorly
   > optimized -- which it is, in general, and this patch chips away at that
   > only a tiny bit, not enough to make it recommendable.
   
   Agreed, but it was very simple to code, so I figured why not.
   
   > Plus I don't see why anyone would write the specific case
   > "IS [NOT] DISTINCT FROM NULL" when they could write half as much.
   
   Well I can see how it might be useful in generated queries, when
   comparing a column to a parameter. If they're using IS DISTINCT FROM
   then it's reasonable to expect that the parameter could be NULL
   sometimes.
   
   But I don't feel strongly about this, maybe it's not worth
   complicating this big function further. :)
   
   Regards,
   Marti
   
   

4. Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-08T21:20:37Z

   Marti Raudsepp <marti@juffo.org> writes:
   > But I don't feel strongly about this, maybe it's not worth
   > complicating this big function further. :)
   
   Yeah, that was kind of what I felt about it.  If this patch were part of
   a grand plan to make IS DISTINCT FROM smarter, that would be one thing.
   But if we were to embark on that, likely as not it would involve a
   redesign that would invalidate this code anyway.  So I'd just as soon
   keep it simple for now.
   
   			regards, tom lane