Materialized View Design

Materialized view design relies on similarity consolidation and finding common sets to avoid redundant work.

The system analyzes historical high-frequency similar SQL statements (based on Unique SQL ID), extracts common subqueries, and creates materialized views. The procedure is as follows:

1. Analyze the top SQL statements.

   1 2 3 4 5

   SELECT unique_sql_id, count(*) as freq FROM pgxc_wlm_session_info WHERE query LIKE '%table_name%' GROUP BY unique_sql_id ORDER BY freq DESC;

2. Summarize common calculations.
   • Similar aggregations on the same table.
   • Different queries on the same table.
   • Common filter criteria combinations.
3. Design materialized views.
   • Basic and simple calculations are usd.
   • The reuse frequency is maximized.
   • The bottom-layer view is materialized preferentially.