Re: missing optimization - column <> column

2016-12-05 16:23 GMT+01:00 Stephen Frost <sfrost(at)snowman(dot)net>:

> Pavel,
>
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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.
>

it should be false everywhere

I don't defend a design of the application - it is exactly wrong. But
sometimes, it can be generated by some tool or it can be a human error. And
bad performance can be a big problems on systems, where you cannot to
deploy fix simply.

It is hard to say what should be good design - because these queries are
slow, I know so these queries are wrong, but these queries does significant
IO utilization - and I cannot to fix the application, because I am not a
author and the fix will not be available in next week.

Regards

Pavel

>
> > 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
>