Re: missing optimization - column <> column

On Dec 5, 2016, at 7:28 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2016-12-05 16:24 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.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.

understand
 

If you write something like "1 <> 1", it will be folded.

it works, but a <> a not

Regards

Pavel

                        regards, tom lane