[davidrowley]

(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.

[maximegarcia]

That would be nice to also optimize SELECT DISTINCT foo FROM bar. It is usually very poor on big tables and we have to do recursive CTE. This comes a lot with admin builders for filters (<select >).

[davidrowley]

SELECT DISTINCT has seen quite a bit of work over the past few years. As of PG15, SELECT DISTINCT can use parallel query. I imagine that might help for big tables.

I assume the recursive CTEs comment is skip scanning using an index and looking for the first value higher than the previously seen value?

Certainly skip scans would be nice. There has been some work in this area, but not recently. As far as I recall some other infrastructure needed to go in first to make it easier for the query planner to understand when skip scanning would be useful.