From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: missing optimization - column <> column |
Date: | 2016-12-05 15:41:22 |
Message-ID: | 20161205154121.GC23417@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Pavel,
* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2016-12-05 16:23 GMT+01:00 Stephen Frost <sfrost(at)snowman(dot)net>:
> > * 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
No, it's NULL, not false, if 'a' is NULL:
=# SELECT 1 WHERE (NULL <> NULL) IS NULL;
?column?
----------
1
(1 row)
=*# SELECT 1 WHERE (FALSE) IS NULL;
?column?
----------
(0 rows)
You can not make the assumption that 'a <> a' is always false.
Thanks!
Stephen
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-12-05 15:46:59 | Re: missing optimization - column <> column |
Previous Message | Pavel Stehule | 2016-12-05 15:35:12 | Re: missing optimization - column <> column |