On Wed, 2021-06-30 at 14:21 +0300, Andrey Lepikhov wrote: > I think, here we could ask more general question: do we want to remove a > 'IS NOT NULL' clause from the clause list if the rest of the list > implicitly implies it? > > EXPLAIN (ANALYZE, VERBOSE) > SELECT * > FROM a WHERE (X IS NOT NULL) AND (X IS NULL); > QUERY PLAN > > ---------------------------------------------------------------------------------------------------- > Seq Scan on public.a (cost=0.00..15.00 rows=87 width=4) (actual > time=0.136..0.136 rows=0 loops=1) > Output: x > Filter: ((a.x IS NOT NULL) AND (a.x IS NULL)) > Rows Removed by Filter: 1000 > > It could reduce a number of selectivity mistakes, but increase CPU > consumption. > If we had such a clause analyzing machinery, we could trivially remove > this unneeded qual.
On the other hand, sometimes something like that can be used to change the optimizer's estimates to encourage certain plans. We also don't optimize "ORDER BY x + 0" (which now you can use to prevent an index scan) or the famous OFFSET 0, partly because it saves planning time, partly because those can be useful tools. Generally I have the impression that we are not too keen on spending planning time on optimizing cases that can be trivially improved by rewriting the query. Yours, Laurenz Albe
