Efficient IN Operator Queries in Postgres

It'd be interesting to see some discussion on how much of this could/should pushed into the database query optimizer proper.

Here it's showing that the application can have more knowledge about the "shape" of the data, or that there are certain unneeded (expensive) constraints placed by query syntax. The question I guess then becomes are there gaps in the query or schema language(s) that would allow this to be expressed at the data layer without this complexity at the application?