From: | Pantelis Theodosiou <ypercube(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: missing optimization - column <> column |
Date: | 2016-12-05 16:04:30 |
Message-ID: | CAE3TBxyjpAXLpgLkZTVFt+95J7_e4TjXEKRYfXv1o-8daW+3uQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Dec 5, 2016 at 3:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > I found some crazy queries in one customer application. These queries are
> > stupid, but it was surprise for me so there are not some simple
> optimization
>
> > create table foo(a int);
> > insert into foo select generate_series(1,100000);
> > analyze foo;
> > explain select * from foo where a <> a;
>
> > It does full scan of foo, although it should be replaced by false in
> > planner time.
>
> > Same issue is a expression a = a .. can be replaced by true
>
> Wrong; those expressions yield NULL for NULL input. You could perhaps
> optimize them slightly into some form of is-null test, but it hardly
> seems worth the planner cycles to check for.
>
> If you write something like "1 <> 1", it will be folded.
>
> regards, tom lane
>
>
>
Would it be worth replacing the condition with the equivalent?
I mean would that help optimizing better some queries when it knows that a
is (not) nullable or when "a" is more complicated expression?
a <> a : (a IS NULL) AND NULL
a = a : (a IS NOT NULL) OR NULL
Pantelis Theodosiou
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-12-05 16:17:47 | Re: Time to retire Windows XP buildfarm host? |
Previous Message | Robert Haas | 2016-12-05 16:00:49 | Re: [COMMITTERS] pgsql: Add max_parallel_workers GUC. |