This is a very real problem I bang my head against regularly. There just seems no way to achieve all three of readability, maintainability and performance in a large enough SQL codebase. You can piece together views right up until the moment the query planner forgets to push where clauses down. You can wrap a query in a function to guarantee the where clause is evaluated then and there, but now you have to maintain that _and_ your view/table. You can slowly rewrite your code bit by bit, adding complexity to force the query planner (in this supposedly declarative language) to behave the way you want it to, for identical results. You can wrap all of it in a materialized view so you don't have to care about performance anymore, right up until the point it takes 12 hours to refresh.

I've be very much in the market for something more modular than SQL, which had a much more customisable query planner so you could say "I don't care if you go away and compile this for an hour as long as you make it as quick as physically possible and then save the query plan forever".