AnalyticDB:Use real-time materialized views to accelerate queries that contain variable parameters

When to use this approach

Use real-time materialized views with query rewrite for variable-parameter queries when all of the following are true:

• The query runs frequently with different parameter values (for example, different date ranges).

• The parameter values fall within a predictable range (for example, an interval between 60 and 120 days).

• The query is resource-intensive on the base table.

• You can accept the storage and maintenance cost of maintaining the materialized view.

If the parameter range is unbounded or unpredictable, query rewrite cannot apply and the query falls back to a full scan of the base table.

How query rewrite works for variable parameters

Query rewrite for variable parameters relies on two mechanisms.

Re-aggregation for supplement

In SQL, the WHERE clause executes before GROUP BY. If a query groups by l_returnflag and l_linestatus but filters on a variable l_shipdate, the materialized view must include l_shipdate in its GROUP BY clause. At query time, query rewrite applies the dynamic WHERE filter on the view and re-aggregates the results on l_returnflag and l_linestatus.

The AVG function does not support re-aggregation directly. Query rewrite handles this through the expression supplement mechanism: it stores SUM and COUNT in the materialized view separately, then computes AVG = SUM / COUNT at query time.

WHERE condition range matching

Query rewrite checks whether the WHERE condition in the actual query is a subset of the condition in the materialized view. If the view covers l_shipdate <= date '1998-12-01' - interval '60' day, any query with an interval between 60 and 120 days falls within that range. Query rewrite automatically supplements the required filter and redirects the query to scan the view.

Prerequisites

Before you begin, make sure you have:

• An AnalyticDB for PostgreSQL instance

• Permission to create tables and materialized views in the target database

Set up the table and materialized view

1. Create the lineitem table.

   CREATE TABLE lineitem (
       l_orderkey    bigint not null,
       l_partkey     integer not null,
       l_suppkey     integer not null,
       l_linenumber  integer not null,
       l_quantity    numeric not null,
       l_extendedprice  numeric not null,
       l_discount    numeric not null,
       l_tax         numeric not null,
       l_returnflag  "char" not null,
       l_linestatus  "char" not null,
       l_shipdate    date not null,
       l_commitdate  date not null,
       l_receiptdate date not null,
       l_shipinstruct char(25) not null,
       l_shipmode     char(10) not null,
       l_comment      varchar(44) not null
   ) DISTRIBUTED BY (l_orderkey);

2. Review the TPC-H Q1 query that you want to accelerate. The variable parameter $1 in the WHERE clause ranges from 60 to 120.

   SELECT l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
   FROM
       lineitem
   WHERE
       l_shipdate <= date '1998-12-01' - interval '$1' day  -- $1 ranges from 60 to 120
   GROUP BY
       l_returnflag,
       l_linestatus
   ORDER BY
       l_returnflag,
       l_linestatus
   LIMIT 1;

3. Create the real-time materialized view q1_mv. The view design reflects both query rewrite mechanisms:

   • l_shipdate is added to GROUP BY to support re-aggregation when the variable WHERE filter is applied.

   • The WHERE condition is narrowed to the lower bound of the dynamic range (interval '60' day), so any query with an interval up to 120 days falls within the view's range.

   • SUM and COUNT columns are added for each AVG in the original query, because AVG does not support re-aggregation directly.

   CREATE INCREMENTAL MATERIALIZED VIEW q1_mv
   AS
   SELECT
       l_returnflag,
       l_linestatus,
       l_shipdate,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
       count(*) as count_order,
       sum(l_extendedprice) as sum_price,
       sum(l_discount) as sum_disc,
       count(l_quantity) as count_qty,
       count(l_extendedprice) as count_price,
       count(l_discount) as count_disc
   FROM
       lineitem
   WHERE
       l_shipdate <= date '1998-12-01' - interval '60' day
   GROUP BY
       l_returnflag,
       l_linestatus,
       l_shipdate
   DISTRIBUTED BY (l_returnflag, l_linestatus);

Verify that query rewrite uses the materialized view

Run EXPLAIN on the original query with a specific interval value within the 60–120 range (for example, 100). The query plan should show a scan on q1_mv instead of lineitem.

EXPLAIN SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
FROM
    lineitem
WHERE
    l_shipdate <= date '1998-12-01' - interval '100' day
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus
LIMIT 1;

The expected query plan shows Seq Scan on q1_mv, confirming that query rewrite is active:

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.01..1.13 rows=1 width=234)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=1.01..1.13 rows=1 width=234)
         Merge Key: l_returnflag, l_linestatus
         ->  Limit  (cost=1.01..1.11 rows=1 width=234)
               ->  GroupAggregate  (cost=1.01..1.11 rows=1 width=234)
                     Group Key: l_returnflag, l_linestatus
                     ->  Sort  (cost=1.01..1.01 rows=1 width=194)
                           Sort Key: l_returnflag, l_linestatus
                           ->  Seq Scan on q1_mv  (cost=0.00..1.00 rows=1 width=194)
                                 Filter: (l_shipdate <= '1998-08-23 00:00:00'::timestamp without time zone)
 Optimizer: Postgres query optimizer
(11 rows)

The Filter line confirms that query rewrite applied the variable condition (interval '100' day evaluates to 1998-08-23) as a filter on the materialized view data, rather than scanning lineitem.

Performance results

A TPC-H test on 1 TB of data, run on an AnalyticDB for PostgreSQL instance with 16 compute nodes, shows the following impact:

Related topics

• Real-time materialized views

• Query rewrite for materialized views