Lists: | pgsql-hackers |
---|
From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL |
Date: | 2012-03-08 17:00:58 |
Message-ID: | CABRT9RAJqPOJ9Wkjg-g1H8-ov2DWXoC=-2q5bkxT=xmq-39pGA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
distinct-to-nulltest.patch | text/x-patch | 3.2 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL |
Date: | 2012-03-08 17:35:36 |
Message-ID: | 13411.1331228136@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Marti Raudsepp <marti(at)juffo(dot)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
From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL |
Date: | 2012-03-08 18:16:00 |
Message-ID: | CABRT9RBxjbMu00EHGb0Gg5FG4-HXfDkzYdq62GQPvUuEOHAeeQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, Mar 8, 2012 at 19:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] Optimize IS DISTINCT FROM NULL => IS NOT NULL |
Date: | 2012-03-08 21:20:37 |
Message-ID: | 7472.1331241637@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Marti Raudsepp <marti(at)juffo(dot)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