Hologres:Dynamic table query rewrite

Enable query rewrite at session level

Set the hg_enable_query_rewrite GUC (General Utility Configuration) parameter before running queries that should benefit from rewrite:

-- Enable query rewrite for the current session
SET hg_enable_query_rewrite = on;

-- Database-level setting (not recommended)
ALTER DATABASE <db_name> SET hg_enable_query_rewrite = on;

Mark a Dynamic Table as a rewrite candidate

When creating a Dynamic Table, set allowed_to_rewrite_query = 'true' to allow it to participate in query rewrite. By default, Dynamic Tables do not participate.

CREATE [ OR REPLACE ] DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name> (
  [col_name],
  [col_name],
  [col_name]
)
[LOGICAL PARTITION BY LIST(<partition_key>)]
WITH (
  ...,
  allowed_to_rewrite_query = '[true | false]',
  ...
)
AS
<query>;

To change this setting on an existing Dynamic Table:

ALTER DYNAMIC TABLE [IF EXISTS] [<schema_name>.]<table_name>
SET (allowed_to_rewrite_query = '[true | false]');

Control which Dynamic Tables are candidates

When multiple Dynamic Tables exist, use a query hint to limit the optimizer's candidate set. This narrows the search space and controls priority.

SELECT /*+HINT query_rewrite_candidates(<schema.dt_name1> <schema.dt_name2> ...) */
  ...
FROM ...;

Separate multiple Dynamic Table names with spaces. Schema names are optional.

Example: Restrict rewrite to a single Dynamic Table named dt_sales:

SELECT /*+HINT query_rewrite_candidates(dt_sales) */
  day, hour, min(amount), max(amount)
FROM base_sales_table
GROUP BY day, hour;

For full hint syntax, see Optimize SQL execution with HINT.

Exact match of aggregation dimensions

The optimizer rewrites the query when:

• The GROUP BY dimensions in the query exactly match those in the Dynamic Table definition.

• The query's aggregate functions map directly to existing columns in the Dynamic Table.

• All aggregate function types—including DISTINCT—are supported, as long as the Dynamic Table already contains the corresponding result column.

Setup:

-- Create base table
CREATE TABLE base_sales_table(
    day text not null,
    hour int,
    amount int
);

-- Insert sample data
INSERT INTO base_sales_table
VALUES  ('20250529', 12, 1),
        ('20250529', 12, 2),
        ('20250529', 12, 2),
        ('20250529', 13, 3),
        ('20250530', 13, 4),
        ('20250530', 14, 5),
        ('20250531', 14, 6);

-- Create Dynamic Table with rewrite enabled
CREATE DYNAMIC TABLE dt_sales
WITH (
    freshness = '1 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true'
)
AS
SELECT
    day,
    hour,
    min(amount),
    max(amount),
    sum(amount),
    count(amount),
    count(*) as rows,
    count(1) as rows1,
    count(distinct amount) as cd
FROM base_sales_table
GROUP BY day, hour;

REFRESH TABLE dt_sales;

Query: When the dimensions match exactly, the execution plan shows the Scan operator reading from the Dynamic Table instead of the base table.

EXPLAIN SELECT day, hour, min(amount), max(amount) FROM base_sales_table GROUP BY day, hour;

Aggregation roll-up

The optimizer rewrites the query when:

• The Dynamic Table's GROUP BY dimensions are a superset of the query's GROUP BY dimensions (the Dynamic Table groups by more dimensions than the query).

• The query's aggregate functions can be derived by re-aggregating existing columns in the Dynamic Table.

Supported aggregate functions for roll-up: min, max, count, sum, and avg. DISTINCT-based roll-up is not supported—except in exact-match cases where the Dynamic Table already holds the distinct result.

Aggregate function mapping:

Setup: Use the same base_sales_table and dt_sales from the exact-match example above.

Query example 1: Roll up by day only. The query groups by fewer dimensions than the Dynamic Table, so rewrite applies.

EXPLAIN SELECT day, min(amount), max(amount)
FROM base_sales_table
GROUP BY day;

Query example 2: Roll up using avg. The Dynamic Table provides both sum and count, so avg can be derived.

EXPLAIN SELECT day, sum(amount), count(amount), avg(amount)
FROM base_sales_table
GROUP BY day;

Condition-compensated aggregation roll-up

The optimizer rewrites the query when:

• The base table query includes a WHERE filter, but the Dynamic Table definition does not include any WHERE filter.

• All fields used in the WHERE clause appear in the Dynamic Table's GROUP BY dimensions.

Supported aggregate functions: min, max, count, sum, and avg. DISTINCT is not supported.

Setup: Use the same base_sales_table and dt_sales from the earlier examples.

Query: The WHERE filter uses day, which is in the Dynamic Table's GROUP BY. The optimizer filters Dynamic Table rows by day and then re-aggregates.

EXPLAIN SELECT day, sum(amount), count(amount), avg(amount)
FROM base_sales_table
WHERE day > '20250528' AND day <= '20250531'
GROUP BY day;

Check the execution plan

Run EXPLAIN on the query. In the output, check the table name scanned by the Scan operator. If it shows the Dynamic Table name instead of the base table name, rewrite succeeded.

Check query logs

Query the hologres.hg_query_log system table using the query ID:

SELECT extended_info::json->>'rewrite_query_info'
FROM hologres.hg_query_log
WHERE query_id = '<query_id>';

The rewrite_query_info field indicates which Dynamic Tables were evaluated and what the outcome was.

Successful rewrite — rewrite_succeeded_and_selected_dt contains the name of the Dynamic Table that was used:

{
  "rewrite_succeeded_and_selected_dt": ["public.dt2"],
  "rewrite_succeeded_but_not_selected_dt": ["public.dt1"],
  "rewrite_failed_dt": []
}

Failed rewrite — rewrite_failed_dt contains the Dynamic Table name and the failure reason:

{
  "rewrite_succeeded_and_selected_dt": [],
  "rewrite_succeeded_but_not_selected_dt": [],
  "rewrite_failed_dt": [["public.dt3", {"rewrite_failed_cause": "Doesn't include all query required output columns"}]]
}

Version

Available only in Hologres 4.1 and later.

Consistency

Query rewrite returns the most recent refresh result of the Dynamic Table. Compared to the latest state of the base table, this introduces a delay within a fixed time window. This is weak consistency.

Base table types

If the base table is a Hologres partitioned table, logical partitioning is supported. Physical partitioning is not.

Dynamic Table types

Query definition limits in Dynamic Tables

• Single-table queries only. Multi-table queries are not supported.

• The FILTER clause is not supported—for example, sum(x) FILTER (WHERE ...).

• Extra computed columns that depend on aggregation results are not supported—for example, sum(x)/count(x).

Supported aggregate functions by rewrite pattern

Example 1: Query rewrite with a Hologres internal base table

This example uses the TPC-H (Transaction Processing Performance Council benchmark H) lineitem dataset (100 GB) as the base table. For table creation and data import steps, see One-Click Import of Public Datasets. The Dynamic Table uses incremental refresh and is non-partitioned.

Create a Dynamic Table with rewrite enabled:

CREATE DYNAMIC TABLE dt_lineitem_100g_incremental
WITH (
    freshness = '10 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true')
AS
SELECT
    l_returnflag,
    l_linestatus,
    l_shipdate,
    sum(l_quantity) as sum_qty,
    count(*) as count_order
FROM hologres_dataset_tpch_100g.lineitem
GROUP BY l_returnflag, l_linestatus, l_shipdate;

-- Manually refresh the Dynamic Table
REFRESH DYNAMIC TABLE dt_lineitem_100g_incremental;

Query the base table with rewrite enabled:

SET hg_enable_query_rewrite = on;

EXPLAIN
SELECT
    l_returnflag,
    l_linestatus,
    l_shipdate,
    sum(l_quantity) as sum_qty,
    count(*) as count_order
FROM hologres_dataset_tpch_100g.lineitem
WHERE l_shipdate = '1998-12-01'
GROUP BY l_returnflag, l_linestatus, l_shipdate;

The execution plan shows the query rewritten to scan the Dynamic Table:

Query results from the base table (via rewrite):

 l_returnflag | l_linestatus | l_shipdate | sum_qty  | count_order
--------------+--------------+------------+----------+-------------
 N            | O            | 1998-12-01 | 52841.00 |        2070
(1 row)

Verify by querying the Dynamic Table directly (auto-refresh is disabled; results reflect the last manual refresh):

SELECT
    l_returnflag,
    l_linestatus,
    l_shipdate,
    sum_qty,
    count_order
FROM dt_lineitem_100g_incremental
WHERE l_shipdate = '1998-12-01';

 l_returnflag | l_linestatus | l_shipdate | sum_qty  | count_order
--------------+--------------+------------+----------+-------------
 N            | O            | 1998-12-01 | 52841.00 |        2070
(1 row)

Both queries return identical results, confirming that query rewrite is working correctly.

Example 2: Query rewrite with a Paimon foreign base table

Query rewrite also works when the base table is a Paimon foreign table.

Step 1: Import the TPC-H customer (100 GB) table into Paimon. See Paimon Table.

Step 2: Create a Paimon foreign table in Hologres using the Foreign Data Wrapper (FDW). See Access Paimon Catalog Using DLF.

-- Create a foreign server backed by Paimon via DLF
CREATE SERVER IF NOT EXISTS paimon_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    catalog_type 'paimon',
    metastore_type 'dlf-rest',
    dlf_catalog '<dlf_catalog_name>'
);

-- Import the Paimon schema as foreign tables
IMPORT FOREIGN SCHEMA <schema_name>
LIMIT TO (customer)
FROM SERVER paimon_server INTO public
OPTIONS (if_table_exist 'update');

-- Verify the foreign table is accessible
SELECT * FROM customer LIMIT 5;

Step 3: Create a Dynamic Table that pre-aggregates from the Paimon foreign table:

CREATE DYNAMIC TABLE dt_paimon_customer
WITH (
    freshness = '10 minutes',
    auto_refresh_mode='incremental',
    auto_refresh_enable='false',
    allowed_to_rewrite_query='true')
AS
SELECT
    c_custkey,
    avg(c_acctbal),
    sum(c_acctbal),
    count(c_acctbal)
FROM customer
GROUP BY c_custkey;

-- Manually refresh the Dynamic Table
REFRESH DYNAMIC TABLE dt_paimon_customer;

Step 4: Query the Paimon foreign table with rewrite enabled:

SET hg_enable_query_rewrite = on;

SELECT
    c_custkey,
    avg(c_acctbal),
    sum(c_acctbal),
    count(c_acctbal)
FROM customer
GROUP BY c_custkey
ORDER BY 3 DESC
LIMIT 3;

c_custkey  | avg          | sum     | count
-----------+--------------+---------+-------
3605586    | 9999.990000  | 9999.99 | 1
10705496   | 9999.990000  | 9999.99 | 1
14959900   | 9999.990000  | 9999.99 | 1

Verify by querying the Dynamic Table directly:

SELECT * FROM dt_paimon_customer ORDER BY 3 DESC LIMIT 3;

c_custkey  | avg          | sum     | count
-----------+--------------+---------+-------
3605586    | 9999.990000  | 9999.99 | 1
10705496   | 9999.990000  | 9999.99 | 1
14959900   | 9999.990000  | 9999.99 | 1

Confirm via execution plan: The plan shows the query rewritten to access the Dynamic Table.