One stuff to keep in mind is that Partial index, index are internal implementation of the database. If you start using materialized view it is visible to the application.
I think you are adding another entity that you must now support and that the application must specifically call.
If your application supports multiple databases, i don't think you want to have specific code to support optimization. Of course you need to do this :)