CODEWITHSUNDEEP
sqlpostgresqlviewdatabase-indexes
Ozzy
Ozzy

Reputation: 974

PostgreSQL: If I define 1-to-1 views for each of my tables and query only from my view layer, will the indexes of the underlying tables be used?

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.

Upvotes: 2

Views: 40

Answers (1)

gotqn
gotqn

Reputation: 43636

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.

Upvotes: 2

Related Questions