The fun thing is, they have corrected a bunch of that in newer versions of postgresql.

I'm surprised that the problem stemmed from an index where the 'AND NOT NULL' took forevery to scan, and they didn't include a partial index, or an index in a different order since they are sorted.

Wasn't this solved in Postgres 12?[0]

[0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

Depends if materialization helps.

As a silly example, if the CTE materializes 1/100th of a table via table scan, scanning 1/100th of a table N times may be faster than scanning the entire table N times.

That could even be a feature if used to compute just the list of active jobs. Later pgs might need to be explicit to reinsert the optimization barrier, as the implicit one for all Cates was removed.

Mostly no, since PostgreSQL 12