Re: remove_useless_joins vs. bug #19560

Tender Wang <tndrwang(at)gmail(dot)com> writes:
> I think this issue is still worth fixing. The discussion in [1] has
> been inactive for about two months, so I wanted to ask whether you
> intend to address it as part of the analyzejoins.c rewrite, or whether
> it would be better for me to resume work on [1] once the rewrite has
> been committed.

I disagree. That's not a bug -- okay, it's a missed optimization for
a small class of poorly-written queries -- and it's not specific to
SJE either. I think far too much development effort has been spent
on the point already. See my upthread reply to Richard [1].

(BTW, sitting here in the light of morning, a totally different
approach occurs to me. The reason that the user-written IS NOT NULL
is useless is that it's testing the result of a joined column,
where the join operator is strict and the join isn't null-extending.
I wonder if we could detect that and then throw away the upper IS NOT
NULL, independently of any join-elimination strategy. I think there
is precedent for this idea in the SQL standard's idea of known
dependencies within queries. It might still not be worth the planner
cycles, but ...)

regards, tom lane

[1] https://www.postgresql.org/message-id/3968565.1785170019%40sss.pgh.pa.us