Materialized views for Postgres Query Optimization:

Materialized views are precomputed and stored views created in SQL, which can be manually refreshed by writing another SQL query, as and when needed.

How are materialized views different from regular views?

A view is like a virtual table which is created for various reasons including security, query optimization and organizing code. A view can be joined like any other table in the database to get the desired output. Both views and materialized views are set of select statements. Materialized views, are similar to regular views, except the fact that their result is stored in dedicated tablespace or disk in the database. Because their result is stored and kept for usage, materialized views take storage space and they are faster in processing. Views are relatively slow in processing, as the query is executed on the fly.

Let's look at an example of a materialized view and how it can be used in query optimization.

The dataset which I am using for demo is Diabetes dataset. It has Patients Table with columns Gender_ID and height and another table Lab_test with WBC column.

Let's count Gender by Gender ID by creating materialized view.

How to call a materialized view?

Materialized views can be called like any other table in the database.

select * from mv_g

Because the value of materialized views are stored when we first run the create statement, if we change any values in the database, we need to refresh the materialized view for the updated results.

refresh materialized view mv_g

Using joins in materialized view:

Now, let's join two tables to get one column from first table and another column from the second table to create a materialized view.

Using Materialized view in another query:

The materialized view is now treated as a table and we can call it like any other query.

Materialized views provide faster results, as the query and the result is pre-stored.

Query Optimization using Materialized View:

Here I am taking a scenario in which I have few tables, the E-R diagram is given under for reference.

Here, we have to find the food that coincided with the time of highest blood sugar for

every patient.

A simple query we can write is:

As you can see, the above query is taking around 14 seconds to display the results. If its taking this much time for a small database, it will take much more time when database is extrapolated.

Let's write the query using Materialized view now and observe how much time will it take to display same result.

In this query, we are first creating a materialized view to calculate max glucose value.

Now, we are using the materialized view to write a query which can give us the same result.

By using materialized view, the query time is reduced from 14 seconds to less than one second.

In conclusion, materialized views are one of the options for Performance tuning and Query optimization. The ability to re-use pre-computed data to give faster results make them improve overall performance.

Thanks for reading!