Materialized Views - Caching Data in the Database to Make Views Much Faster

The purpose of this page is to help you increase the query performance frequently called, long-running queries. There are times when you need to show aggregated data, and the iDempiere dashboard is the perfect example. The problem is that iDempiere updates the dashboard of every user (1) every time he/she logs in and (2) ever 60 seconds for as long as he/she is logged in. If the database views that supply this aggregated data are are anything less that immediate, you will see iDempiere start to slow down due to the added load. The PostgreSQL Materialized view is potentially a great solution for this situation. Below are great links to help you learn and use materialized views:

PostgreSQL Materialized View Datasheet - Note that you can specify an automatic refresh schedule when you create the materialized view
Great article with practical examples and strategies

Optimizing for Like Searches

Reference: https://niallburkley.com/blog/index-columns-for-like-in-postgres/

CREATE EXTENSION pg_trgm;

CREATE INDEX chuboe_csr_bp_info_mv_idx ON chuboe_csr_bp_info_mv USING gin (Chuboe_Address_List gin_trgm_ops);

Refreshing Materialized Views

Consider using 'concurrently' where the materialized view has at least one unique index for non-blocking refreshes.

Other Notes

In Oracle, it is considered bad practice to query a materialized view directly. Instead, you create the materialized view and Oracle will automatically use it in your existing views if it deems the materialized view applicable. I have not seen such a discussion for PostgreSQL. Please let me to documentation if you see such a discussion. In the meantime, I would just query the materialized view directly.