Re: missing optimization - column <> column - Mailing list pgsql-hackers

From Stephen Frost
Subject Re: missing optimization - column <> column
Date
Msg-id 20161205152352.GB23417@tamriel.snowman.net
Whole thread Raw
In response to missing optimization - column <> column  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: missing optimization - column <> column  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
Pavel,

* Pavel Stehule (pavel.stehule@gmail.com) wrote:
> 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.

a <> a could go to NULL.  Obviously, that'll be false for such a simple
case, but it might not work out that way in a more complicated WHERE
clause.

> Same issue is a expression a = a .. can be replaced by true

a = a can't be replaced unless you know that 'a' can't be NULL.

In short, fix the application.

Thanks!

Stephen

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: missing optimization - column <> column
Next
From: Pavel Stehule
Date:
Subject: Re: missing optimization - column <> column