I am using PostgreSQL and I want to create a layer of views on top of all the tables in my database schema. I will implement a 1-to-1 mapping of view to table, so these views will not have joins in them. The purpose is to provide a read-only abstraction so that I can change the underlying table structure of the database over time but control what is exposed through the views.
The question I have is when I start querying (SELECT statements only) using the views, including some complex joins and other complex query dynamics like aggregation/grouping, will PostgreSQL make use of the indexes on the underlying tables as if I was querying them directly?
I am starting with a PoC of this now. I don't have any results yet, but wanted to hear from other people's knowledge, experiences and opinions.
Yes, the engine will use available indexes and optimize the code. It will basically replace the view with its definition and build the plan.
Here you can fine some example and test it further.