Use Automatic Materialized Views

SynxDB supports automatically using materialized views to process some or all queries (called “Answer Query Using Materialized Views”, or “AQUMV”) during the query planning phase. This feature is suitable for queries on large tables and can greatly reduce query processing time. AQUMV uses incremental materialized views (IMVs) because IMVs usually keep the latest data when related tables have write operations.

Usage scenarios

Aggregation queries on large data sets: For queries that need to aggregate results from millions of records, AQUMV can significantly reduce query time.
Frequently updated large tables: In an environment where data is frequently updated, using IMV can ensure that the query results are real-time and accurate.
Complex calculations: For queries with complex calculations (such as square root and absolute value calculations), AQUMV can speed up the queries by pre-calculating these values in materialized views.

Implementation

AQUMV implements query optimization by equivalently transforming the query tree.

SynxDB automatically uses materialized views only when the table query meets the following conditions:

Materialized views must contain all the rows required by the query expression.
If the materialized view contains more rows than the query, you might need to add additional filters.
All output expressions must be able to be calculated from the output of the view.
The output expression can fully or partially match the target list of the materialized view.

When there are multiple valid materialized view candidates, or the cost of querying from the materialized view is higher than querying directly from the original table (for example, the original table has indexes), you can let the planner make the best choice based on cost estimates.

Comparison with dynamic tables

AQUMV and dynamic tables have these differences:

Feature	Dynamic table	AQUMV
Purpose	A special table that automatically refreshes, processes data pipelines, and simplifies ETL.	Uses materialized views to improve query efficiency and automatically rewrite queries.
Base and structure	Can be based on ordinary tables, external tables, materialized views.	Based on materialized views, usually targeting a single table.
Query rewrite	Not supported	Supports single-table rewrite and multi-table JOIN exact-match rewrite
Data refresh mechanism	Users can define automatic refresh interval through SQL	Incremental materialized views maintain themselves; regular materialized views need a manual `REFRESH`

Restrictions

AQUMV uses two code paths with different capabilities:

Single-table rewrite applies when both the query and the materialized view reference a single base table. The planner can rewrite the query even if its filters, target expressions, or aggregates only partially overlap the materialized view, as long as the materialized view contains all required rows and every output expression can be computed from its target list. This path has the following limitations:
- Support for external tables requires explicitly setting aqumv_allow_foreign_table to ON.
- The HAVING clause must be calculable from the target list of the materialized view.
- This path does not rewrite queries that use subqueries, sublinks, ORDER BY, DISTINCT ON, window functions, common table expressions (CTE), REFRESH MATERIALIZED VIEW, or CREATE AS statements.
Multi-table JOIN exact-match rewrite applies when the query references more than one base table. The planner only rewrites a query whose structure is identical to the materialized view definition. See multi-table join exact-match support for the comparison rules and limitations.

Usage examples

To enable AQUMV, you need to create a materialized view, turn off the GPORCA optimizer, and set the value of the system parameter enable_answer_query_using_materialized_views to ON. The following example compares the results of the same complex query without AQUMV and with AQUMV.

Turn off the GPORCA planner to use the Postgres-based planner.
```
SET optimizer TO off;
```

Create a table aqumv_t1.

CREATE TABLE aqumv_t1(c1 INT, c2 INT, c3 INT) DISTRIBUTED BY (c1);

Insert data into the table and collect statistics from the table.

INSERT INTO aqumv_t1 SELECT i, i+1, i+2 FROM generate_series(1, 1000000) i;
ANALYZE aqumv_t1;

Execute a query without enabling AQUMV. The timings below come from a 4-segment cluster and are indicative of magnitude only.

SELECT SQRT(ABS(ABS(c2) - c1 - 1) + ABS(c2)) FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40 AND SQRT(ABS(c2)) > 5.8;

       sqrt
-------------------
 5.830951894845301
 6.082762530298219
 6.164414002968976
 6.324555320336759
                 6
 6.244997998398398
 5.916079783099616
(7 rows)

Time: 31.467 ms

From the following query plan, you can see that the optimizer scans the table (Seq Scan on aqumv_t1).

EXPLAIN(COSTS OFF) SELECT SQRT(ABS(ABS(c2) - c1 - 1) + ABS(c2)) FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40 AND SQRT(ABS(c2)) > 5.8;

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Seq Scan on aqumv_t1
         Filter: ((c1 > 30) AND (c1 < 40) AND (sqrt((abs(c2))::double precision) > '5.8'::double precision))
 Optimizer: Postgres query optimizer
(4 rows)

Create a materialized view mvt1 based on aqumv_t1 and collect statistics on the view.

CREATE INCREMENTAL MATERIALIZED VIEW mvt1 AS SELECT c1 AS mc1, c2 AS mc2, ABS(c2) AS mc3, ABS(ABS(c2) - c1 - 1) AS mc4
FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40;

ANALYZE mvt1;

Enable the AQUMV-related configuration parameter.

SET enable_answer_query_using_materialized_views = ON;

Now AQUMV is enabled. Execute the same query again.

SELECT SQRT(ABS(ABS(c2) - c1 - 1) + ABS(c2)) FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40 AND SQRT(ABS(c2)) > 5.8;

       sqrt
-------------------
 6.164414002968976
                 6
 5.916079783099616
 6.244997998398398
 5.830951894845301
 6.082762530298219
 6.324555320336759
(7 rows)

Time: 1.026 ms

From the following query plan, you can see that the optimizer does not scan the aqumv_t1 table, but scans the materialized view mvt1 (Seq Scan on public.mvt1) instead.

EXPLAIN(VERBOSE, COSTS OFF) SELECT SQRT(ABS(ABS(c2) - c1 - 1) + ABS(c2)) FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40 AND SQRT(ABS(c2)) > 5.8;

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   Output: (sqrt(((mc4 + mc3))::double precision))
   ->  Seq Scan on public.mvt1
         Output: sqrt(((mc4 + mc3))::double precision)
         Filter: (sqrt((mvt1.mc3)::double precision) > '5.8'::double precision)
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer
(7 rows)

In the above example, the query takes 31.467 ms without AQUMV, and only 1.026 ms with AQUMV enabled and using the materialized view. This means that the materialized view pre-calculates and stores relevant calculation result, so that the view only contains rows that meet the specific condition (c1 > 30 AND c1 < 40).

Therefore, the above table query SELECT SQRT(ABS(ABS(c2) - c1 - 1) + ABS(c2)) FROM aqumv_t1 WHERE c1 > 30 AND c1 < 40 AND SQRT(ABS(c2)) > 5.8; is equivalent to querying from the materialized view SELECT SQRT(mc4 + mc3) FROM mvt1 WHERE SQRT(mc3) > 5.8;.

When the same query is executed, the data can be obtained directly from the materialized view, rather than from the original table. In this way, AQUMV can significantly improve query performance, especially when dealing with large data volumes and complex calculations.

Use materialized views to query external tables

SynxDB supports querying external tables using materialized views. In general, querying external tables faces challenges like data synchronization latency and network bottlenecks. Materialized views can cache query results to avoid accessing external data sources every time, which improves query speed.

Here is an example:

Register the sample protocol demoprot. It is provided by gpextprotocol.so, which ships with SynxDB, but it is not registered by default.

CREATE OR REPLACE FUNCTION demoprot_export() RETURNS integer AS '$libdir/gpextprotocol', 'demoprot_export' LANGUAGE C STABLE;
CREATE OR REPLACE FUNCTION demoprot_import() RETURNS integer AS '$libdir/gpextprotocol', 'demoprot_import' LANGUAGE C STABLE;
CREATE TRUSTED PROTOCOL demoprot (writefunc = demoprot_export, readfunc = demoprot_import);

In real use, substitute a protocol such as gpfdist or file for demoprot; the remaining steps are unaffected.

Prepare the data file for the external table. This uses a writable external table to write the data out. A demoprot path is relative to each segment’s data directory, and every segment writes its own copy of the file.

CREATE WRITABLE EXTERNAL TABLE aqumv_ext_w(id int)
LOCATION ('demoprot://aqumvtextfile.txt')
FORMAT 'text';

CREATE TABLE aqumv_src(id int) DISTRIBUTED BY (id);
INSERT INTO aqumv_src SELECT i FROM generate_series(1, 1000000) i;
INSERT INTO aqumv_ext_w SELECT id FROM aqumv_src;

Create an external table and its corresponding materialized view.

CREATE READABLE EXTERNAL TABLE aqumv_ext_r(id int)
LOCATION ('demoprot://aqumvtextfile.txt')
FORMAT 'text';

CREATE MATERIALIZED VIEW aqumv_ext_mv AS
SELECT * FROM aqumv_ext_r DISTRIBUTED BY (id);
ANALYZE aqumv_ext_mv;

Enable the AQUMV support in a transaction for the external table:

BEGIN;
SET optimizer = OFF;
SET aqumv_allow_foreign_table = ON;
SET enable_answer_query_using_materialized_views = ON;

Check the query plan to see that the query uses the materialized view instead of directly scanning the external table:

EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM aqumv_ext_r;

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   Output: id
   ->  Seq Scan on public.aqumv_ext_mv
         Output: id
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer

Create an index on the materialized view to further optimize queries:
```
CREATE INDEX ON aqumv_ext_mv(id);
ANALYZE aqumv_ext_mv;
```

When the conditional query is executed, the query plan shows the use of the index:

EXPLAIN (COSTS OFF, VERBOSE)
SELECT * FROM aqumv_ext_r WHERE id = 5;

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)
   Output: id
   ->  Index Only Scan using aqumv_ext_mv_id_idx on public.aqumv_ext_mv
         Output: id
         Index Cond: (aqumv_ext_mv.id = 5)
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer

Note

This example uses 1 million rows. When the materialized view holds very few rows, the planner might find a sequential scan cheaper and produce a Seq Scan instead of an Index Only Scan.

Commit the transaction:

COMMIT;

When this feature is enabled, queries on external tables automatically uses materialized views, including leveraging indexes on materialized views to accelerate queries.

Note

Because external table data might change outside the database and the planner cannot detect these changes, AQUMV is disabled for external tables by default.
It is recommended to enable aqumv_allow_foreign_table only when you are certain that external table data has not changed.
For some external tables (like Iceberg and Hudi), access speed can be 10 times slower or more than internal tables. Using materialized views in these cases can greatly improve query performance.
Compared to importing external table data into internal tables, using materialized views avoids data loading latency and time overhead.

Aggregate query support

SynxDB supports optimizing queries that contain aggregate functions using materialized views. This feature can greatly improve query performance when dealing with aggregate operations on large datasets.

Aggregate query support

Create a table t.

CREATE TABLE t(c1 INT, c2 INT, c3 INT) DISTRIBUTED BY (c1);

Insert data into the table and collect statistics.

INSERT INTO t SELECT i, i+1, i+2 FROM generate_series(1, 1000000) i;
ANALYZE t;

Create a materialized view with aggregate functions.

CREATE MATERIALIZED VIEW mv AS
SELECT sum(c1) AS mc1, count(c2) AS mc2,
      avg(c3) AS mc3, count(*) AS mc4
FROM t
WHERE c1 > 90;

Query transformation example.

Original query:

SELECT count(*), sum(c1), count(c2), avg(c3),
      abs(count(*) - 21) 
FROM t
WHERE c1 > 90;

AQUMV will automatically rewrite it as:

SELECT mc4, mc1, mc2, mc3, abs((mc4 - 21))
FROM mv;

Seq Scan on mv in the query plan shows that the optimizer directly uses the materialized view:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21)
FROM t
WHERE c1 > 90;
                                                QUERY PLAN
------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   Output: mc4, mc1, mc2, mc3, (abs((mc4 - 21)))
   ->  Seq Scan on public.mv
         Output: mc4, mc1, mc2, mc3, abs((mc4 - 21))
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer
(6 rows)

HAVING clause processing

AQUMV supports queries with HAVING clauses. If the conditions in the HAVING clause can be calculated from the target list of the materialized view, these conditions will be converted to WHERE clauses.

Example:

Create a table and materialized view. If you already ran the previous example, drop the objects with the same names first.

DROP MATERIALIZED VIEW IF EXISTS mv;
DROP TABLE IF EXISTS t;
CREATE TABLE t(c1 int, c2 int, c3 int, c4 int) DISTRIBUTED BY (c1);
INSERT INTO t SELECT i, i+1, i+2, i+3 FROM generate_series(1, 1000000) i;
ANALYZE t;
CREATE MATERIALIZED VIEW mv AS
SELECT sum(c1) AS mc1, count(c2) AS mc2,
      avg(c3) AS mc3, count(*) AS mc4
FROM t
WHERE c1 > 90;
ANALYZE mv;

Query transformation with HAVING clause.

Original query:

SELECT count(*), sum(c1)
FROM t
WHERE c1 > 90
HAVING abs(count(*) - 21) > 0 AND 2 > 1 AND avg(c3) > 97;

AQUMV will automatically rewrite it as:

SELECT mc4, mc1
FROM mv
WHERE mc3 > 97 AND abs(mc4 - 21) > 0;

The query plan shows that HAVING conditions are converted to filter conditions in the WHERE clause:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*), sum(c1)
FROM t
WHERE c1 > 90
HAVING abs(count(*) - 21) > 0 AND 2 > 1 AND avg(c3) > 97;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   Output: mc4, mc1
   ->  Seq Scan on public.mv
         Output: mc4, mc1
         Filter: ((mv.mc3 > '97'::numeric) AND (abs((mv.mc4 - 21)) > 0))
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Optimizer: Postgres query optimizer
(7 rows)

Note:

The condition 2 > 1 in the HAVING clause is optimized out during planning.
abs(count(*) - 21) > 0 is converted to abs(mc4 - 21) > 0 and used as a filter condition.

LIMIT and ORDER BY Support

ORDER BY, LIMIT, and OFFSET can all be applied directly to the rewritten materialized view query:

An ORDER BY clause is preserved on the rewritten query, and the ordering semantics are unchanged.

LIMIT and OFFSET clauses with constant values can be directly applied to materialized view queries.

Example:

-- Original query
SELECT count(*), sum(c1)
FROM t
WHERE c1 > 90
LIMIT 2;

-- Rewritten as
SELECT mc4, mc1
FROM mv
LIMIT 2;

Notes for aggregate query support

Aggregate queries with a GROUP BY clause can also be rewritten, but the WHERE predicate of the query must match the predicate in the materialized view definition. When the predicates differ, no rewrite happens and the original aggregate plan is used.
With or without GROUP BY, the target list can differ from the materialized view. You can select fewer columns, and you can wrap further expressions around aggregate results the materialized view already holds. For example, if the materialized view holds count(*), the query can use abs(count(*) - 21).
Conditions in HAVING clauses must be calculable from the target list of the materialized view.
An aggregate query without GROUP BY returns one row or zero rows. With GROUP BY, the number of rows depends on the number of groups.

Multi-table JOIN exact-match support

For queries that join multiple base tables, AQUMV uses a separate code path that rewrites the query to a sequential scan on a materialized view only when the query is structurally identical to the materialized view definition. This lets repeated multi-table analytical queries reuse pre-computed results without requiring users to rewrite the query or maintain a custom rewrite rule.

All join types are eligible, including INNER, LEFT, RIGHT, FULL, implicit cross joins, and self-joins.

Note

Outer joins (LEFT, RIGHT, FULL) cannot be defined as incremental materialized views. To use an outer-join definition with AQUMV, create a regular (non-incremental) materialized view and run REFRESH MATERIALIZED VIEW after base table changes. Inner joins, cross joins, and self-joins can use either incremental or non-incremental materialized views.

Exact-match conditions

AQUMV compares the query against the view definition of each populated, up-to-date materialized view. A match requires identical content in every one of the following:

Range table entries (base table OIDs, join structure, aliases)
FROM clause join tree and WHERE predicates
Target list expressions, including resjunk flags and sort/group references
GROUP BY clause and GROUP BY DISTINCT flag
HAVING clause
ORDER BY clause
DISTINCT clause and DISTINCT ON flag
LIMIT, OFFSET, and the LIMIT option (FETCH FIRST ... WITH TIES versus a plain LIMIT)

If any of these fields differ, the planner keeps the original join plan. The planner also compares the cost of the materialized view scan against the original plan, and AQUMV switches to the materialized view only when the scan is cheaper.

For the rewrite to apply, the materialized view must be populated and up-to-date. Incremental materialized views always satisfy this requirement; for non-incremental materialized views, run REFRESH MATERIALIZED VIEW after base table changes.

Example

The following example shows a two-table JOIN that AQUMV rewrites to a sequential scan on the materialized view.

Turn off the GPORCA planner and enable AQUMV.

SET optimizer TO off;
SET enable_answer_query_using_materialized_views TO on;

Create two base tables and insert data.

CREATE TABLE t1(a int, b int) DISTRIBUTED BY (a);
CREATE TABLE t2(a int, c int) DISTRIBUTED BY (a);
INSERT INTO t1 SELECT i, i + 1 FROM generate_series(1, 100000) i;
INSERT INTO t2 SELECT i, i + 2 FROM generate_series(1, 100000) i;
ANALYZE t1;
ANALYZE t2;

Create an incremental materialized view that joins the two tables.

CREATE INCREMENTAL MATERIALIZED VIEW mv_join AS
 SELECT t1.a, t1.b, t2.c
 FROM t1 JOIN t2 ON t1.a = t2.a;
ANALYZE mv_join;

Run a query whose structure matches the materialized view definition.

EXPLAIN (COSTS OFF)
SELECT t1.a, t1.b, t2.c
FROM t1 JOIN t2 ON t1.a = t2.a;

                QUERY PLAN
------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Seq Scan on mv_join
 Optimizer: Postgres query optimizer
(3 rows)

The plan reads from mv_join instead of computing the hash join.

Add a predicate that the materialized view definition does not include. The structure no longer matches, so AQUMV falls back to the original join plan.

EXPLAIN (COSTS OFF)
SELECT t1.a, t1.b, t2.c
FROM t1 JOIN t2 ON t1.a = t2.a
WHERE t1.a > 100;

                QUERY PLAN
------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Hash Join
         Hash Cond: (t1.a = t2.a)
         ->  Seq Scan on t1
               Filter: (a > 100)
         ->  Hash
               ->  Seq Scan on t2
                     Filter: (a > 100)
 Optimizer: Postgres query optimizer
(9 rows)

Status maintenance for JOIN materialized views

SynxDB tracks the freshness of multi-table JOIN materialized views in the same way as single-table materialized views. When any base table referenced by a JOIN materialized view receives an INSERT, UPDATE, DELETE, TRUNCATE, or VACUUM FULL, SynxDB updates the materialized view’s status in the gp_matview_aux system catalog. Aborting the transaction rolls back the status change.

This applies to all eligible JOIN definitions, including self-joins, cross joins, all outer join types, joins involving partitioned tables, and definitions that combine JOIN with GROUP BY.

To inspect the freshness of a JOIN materialized view, query gp_matview_aux directly:

SELECT mvname, datastatus
FROM gp_matview_aux
WHERE mvname = 'mv_join';

The values of datastatus mean the following:

Value	Meaning
`u`	Up-to-date with respect to the base tables
`i`	The base tables have received inserts
`e`	The base tables have received changes other than inserts

For a non-incremental materialized view, only the u state makes it eligible for a rewrite. Run REFRESH MATERIALIZED VIEW to bring it back to u after base table changes.

An incremental materialized view behaves differently. Its datastatus also becomes i or e after base table changes and does not return to u on its own, but because the view has already applied the incremental maintenance, it remains eligible for a rewrite and the rewritten result is correct. So a datastatus other than u on an incremental materialized view does not mean the view is stale.