Query rewrite for materialized views

SELECT columns

GROUP BY columns

When the materialized view has no GROUP BY and no aggregate functions:

The query can add GROUP BY columns, aggregate functions, or both.

When the materialized view has GROUP BY but no aggregate functions:

When the materialized view has aggregate functions but no GROUP BY:

Adding GROUP BY columns in the query is not supported.

When the materialized view has both GROUP BY and aggregate functions:

JOIN tables

When JOIN tables are identical between the query and the materialized view:

When the query has additional JOIN tables not in the materialized view:

Extra INNER JOIN tables can be supplemented. INNER JOIN and implicit (comma) joins are interchangeable. Additional tables cannot be supplemented for LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN.

Examples of supported patterns when supplementing an INNER JOIN:

-- Materialized view definition
SELECT * FROM a, b;

-- Supported query (adds table c)
SELECT * FROM a, b, c;

-- Materialized view definition
SELECT * FROM a INNER JOIN b ON a.i = b.i;

-- Supported query (adds INNER JOIN c)
SELECT * FROM a INNER JOIN b ON a.i = b.i INNER JOIN c ON a.i = c.i;

WHERE clause

AND conditions: Order is ignored. The query can add AND conditions that reference columns present in the materialized view.

Example:

-- Materialized view definition
SELECT * FROM t WHERE a > 100;

-- Supported query (adds b > 200 as an extra AND condition)
SELECT * FROM t WHERE b > 200 AND a > 100;

OR conditions: Order is ignored. The query can use a narrower range, provided all referenced columns exist in the materialized view.

Example:

-- Materialized view definition
SELECT * FROM t WHERE a > 100 OR a < 200;

-- Supported query (narrower: only one branch)
SELECT * FROM t WHERE a < 200;

Range subsumption: When the materialized view defines a range, the query can use an equality condition or a narrower range within that range. All referenced columns must exist in the materialized view.

-- Materialized view definition
SELECT * FROM t WHERE a < 200 AND a >= 100;

-- Supported query (equality within the range)
SELECT * FROM t WHERE a = 102;

-- Materialized view definition
SELECT * FROM t WHERE a < 200 AND a >= 0;

-- Supported query (narrower range)
SELECT * FROM t WHERE a <= 100 AND a > 50;

HAVING clause

When GROUP BY supplement is not needed, HAVING clause supplement follows the same rules as WHERE clause supplement: missing AND conditions can be added, OR conditions can be narrowed, and ranges can be narrowed.

When GROUP BY supplement is needed, the query can add a HAVING clause even if the materialized view has none.

ORDER BY columns

The query's ORDER BY columns must be a subset of the materialized view's ORDER BY columns. This applies regardless of whether the materialized view includes ORDER BY.

LIMIT clause

Expression supplement

When an expression in the query doesn't directly match the materialized view, query rewrite searches sub-expressions from the top down to find the closest match.

-- Materialized view definition
SELECT a+b, c FROM t;

-- Supported queries (use sub-expression a+b)
SELECT a+b, (a+b)+c, mod(a+b, c) FROM t;
SELECT sum((a+b)*c) FROM t;

For aggregate function expressions:

• SUM() and COUNT() in the materialized view can be combined to satisfy an AVG() in the query.

• COUNT(*) and COUNT(1) are interchangeable.

-- Materialized view definition
SELECT sum(a), count(a), count(*) FROM t;

-- Supported query
SELECT avg(a), count(1) FROM t;

Example 1: Complete match

1. Create a base table:

   CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);

2. Insert data:

   INSERT INTO t1 VALUES (generate_series(1, 10), generate_series(1, 2));

3. Create a real-time materialized view:

   CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT count(a), b FROM t1 GROUP BY b DISTRIBUTED BY (b);

4. Run EXPLAIN on a query identical to the materialized view definition:

   EXPLAIN SELECT count(a), b FROM t1 GROUP BY b;

   Expected output:

   QUERY PLAN
   -----------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.02 rows=2 width=12)
      ->  Seq Scan on mv  (cost=0.00..2.02 rows=1 width=12)
    Optimizer: Postgres query optimizer
   (3 rows)

   The plan shows Seq Scan on mv instead of Seq Scan on t1, confirming a complete match. Query rewrite reads directly from the materialized view.

Example 2: Query supplement (JOIN and WHERE added, GROUP BY narrowed)

1. Create two base tables:

   CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
   CREATE TABLE t2 (i int, j int) DISTRIBUTED BY (i);

2. Insert data:

   INSERT INTO t1 VALUES (generate_series(1, 10), generate_series(1, 2));
   INSERT INTO t2 VALUES (generate_series(1, 10), generate_series(1, 2));

3. Create a real-time materialized view:

   CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT count(a), a, b FROM t1 GROUP BY a, b DISTRIBUTED BY (a);

4. Run EXPLAIN on a query that adds a JOIN and WHERE clause, and uses fewer GROUP BY columns:

   EXPLAIN SELECT count(a) FROM t1 JOIN t2 ON t1.a = t2.i WHERE b > 3 GROUP BY a;

   Expected output:

   QUERY PLAN
   ----------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..437.00 rows=1 width=8)
      ->  Result  (cost=0.00..437.00 rows=1 width=8)
            ->  GroupAggregate  (cost=0.00..437.00 rows=1 width=8)
                  Group Key: mv.a
                  ->  Sort  (cost=0.00..437.00 rows=1 width=12)
                        Sort Key: mv.a
                        ->  Hash Join  (cost=0.00..437.00 rows=1 width=12)
                              Hash Cond: (mv.a = t2.i)
                              ->  Index Scan using mv_index on mv  (cost=0.00..6.00 rows=1 width=12)
                                    Index Cond: (b > 3)
                              ->  Hash  (cost=431.00..431.00 rows=4 width=4)
                                    ->  Seq Scan on t2  (cost=0.00..431.00 rows=4 width=4)
    Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
   (13 rows)

   The plan references mv instead of t1, confirming that query rewrite used the materialized view. Query rewrite supplemented the JOIN to t2, applied the WHERE b > 3 filter via an index scan on the materialized view, and performed re-aggregation to reduce the GROUP BY from (a, b) to (a).