The lack of many “obvious” optimizations such as those in the article in postgres continually frustrates me. Often, I find that a query’s performance can be improved by adding information which the database should be able to infer itself. In particular, I have been bitten by 2, 4, and a variant of 10 before (query plan improved by duplicating a WHERE clause in a subquery which would have still applied due to a JOIN).
This post is from 2017. I wonder if in the last five years, any of these optimizations have been added to databases that didn’t support them at the time.