Re: Removing unneeded self joins

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