Hologres:Accelerate SQL execution with Fixed Plan

Check and set GUC parameters

Check whether a parameter is enabled:

SHOW <guc_name>;

-- Example
SHOW hg_experimental_enable_fixed_dispatcher;

Enable at the session level (valid for the current connection only):

SET <guc_name> = on;

-- Example: enable Fixed Plan for multi-record writes
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = on;

Enable at the database level (takes effect after reconnecting; does not apply to new databases):

ALTER DATABASE <db_name> SET <guc_name> = on;

-- Example
ALTER DATABASE <db_name> SET hg_experimental_enable_fixed_dispatcher = on;

Conditions

INSERT statements qualify for Fixed Plan when all of the following apply:

• The target is an internal table (not a foreign table).

• For partitioned tables, the target is a child table. In V1.3 and later, writing to the parent table is also supported.

Single-record INSERT

The following syntax qualifies:

INSERT INTO table_name (col1, col2, col3, ...) VALUES (?, ?, ?, ...) ON CONFLICT xxx;

For ON CONFLICT DO UPDATE, the update conditions are:

• All non-PK columns listed in the INSERT must be updated, in col = excluded.col format.

• In Hologres V1.3 and later, partial non-PK column updates are supported — you can update a subset of the inserted non-PK columns.

• PK columns may optionally be included in the update.

• Expressions like col2 = 5 (not referencing excluded) are not supported.

BEGIN;
CREATE TABLE test_insert_oneline (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
COMMIT;

-- Qualifies: update all non-PK columns
INSERT INTO test_insert_oneline VALUES (1, 2, 3, 4)
ON CONFLICT (pk1, pk2)
DO UPDATE SET col1 = excluded.col1, col2 = excluded.col2;

-- Qualifies: update all columns including PKs
INSERT INTO test_insert_oneline VALUES (1, 2, 3, 4)
ON CONFLICT (pk1, pk2)
DO UPDATE SET col1 = excluded.col1, col2 = excluded.col2,
              pk1 = excluded.pk1, pk2 = excluded.pk2;

-- Qualifies in V1.3+: partial non-PK column update
INSERT INTO test_insert_oneline VALUES (1, 2, 3, 4)
ON CONFLICT (pk1, pk2)
DO UPDATE SET col1 = excluded.col1;

-- Does not qualify: col2 uses a constant instead of excluded.col2
INSERT INTO test_insert_oneline VALUES (1, 2, 3, 4)
ON CONFLICT (pk1, pk2)
DO UPDATE SET col1 = excluded.col1, col2 = 5;

Multi-record INSERT

Set hg_experimental_enable_fixed_dispatcher_for_multi_values = on before the statement. Starting from V1.3.35, this parameter defaults to on.

Atomicity is not guaranteed for multi-record writes. If an error is reported, some records may have been written and others may not. The application receives the error for the failed records and can retry.

Option 1: VALUES syntax

SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;

INSERT INTO table_name (col1, col2, col3, ...)
VALUES (?, ?, ?...), (?, ?, ?...)
ON CONFLICT xxx;

Option 2: unnest syntax

SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;

INSERT INTO table_name
SELECT unnest(ARRAY[TRUE, FALSE, TRUE]::bool[]),
       unnest(ARRAY[1, 2, 3]::int4[]),
       unnest(ARRAY[1.11, 2.222, 3]::float4[])
ON CONFLICT xxx;

Requirements for the unnest syntax:

• The columns being written cannot be of an array type.

• Each ARRAY in unnest() must be explicitly cast to match the target column type.

BEGIN;
CREATE TABLE test_insert_multiline (
    pk1 int8,
    col1 float4,
    PRIMARY KEY (pk1)
);
COMMIT;

-- Qualifies
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
INSERT INTO test_insert_multiline
SELECT unnest(ARRAY[1, 2, 3]::int8[]),
       unnest(ARRAY[1.11, 2.222, 3]::float4[])
ON CONFLICT DO NOTHING;

-- Does not qualify: ARRAY not explicitly cast
INSERT INTO test_insert_multiline
SELECT unnest(ARRAY[1, 2, 3]),
       unnest(ARRAY[1.11, 2.222, 3])
ON CONFLICT DO NOTHING;

-- Does not qualify: pk1 is int8 but cast to int4[]
INSERT INTO test_insert_multiline
SELECT unnest(ARRAY[1, 2, 3]::int4[]),
       unnest(ARRAY[1.11, 2.222, 3]::float4[])
ON CONFLICT DO NOTHING;

Tables with default values

Fixed Plan supports tables with columns that have default values, with the following conditions:

• Single-record INSERT: always supported.

• Multi-record INSERT: requires Hologres V1.1.36 or later, with hg_experimental_enable_fixed_dispatcher_for_multi_values = on.

• INSERT ON CONFLICT on tables with default columns: supported from V1.3 onwards. Not supported in earlier versions.

BEGIN;
CREATE TABLE test_insert_default (
    pk1 INT,
    col1 INT DEFAULT 99,
    PRIMARY KEY (pk1)
);
COMMIT;

-- Qualifies
INSERT INTO test_insert_default (pk1) VALUES (1);

-- Qualifies in V1.1.36+
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
INSERT INTO test_insert_default (pk1) VALUES (1), (2), (3);

Tables with serial columns

For tables with auto-incrementing serial columns, set hg_experimental_enable_fixed_dispatcher_autofill_series = on. Starting from V1.3.25, this defaults to on.

For multi-record writes, also set hg_experimental_enable_fixed_dispatcher_for_multi_values = on.

BEGIN;
CREATE TABLE test_insert_serial (
    pk1 INT,
    col1 SERIAL,
    PRIMARY KEY (pk1)
);
COMMIT;

-- Qualifies: single-record insert with serial column
SET hg_experimental_enable_fixed_dispatcher_autofill_series = ON;
INSERT INTO test_insert_serial (pk1) VALUES (1);

-- Qualifies: multi-record insert with serial column
SET hg_experimental_enable_fixed_dispatcher_autofill_series = ON;
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
INSERT INTO test_insert_serial (pk1) VALUES (1), (2), (3);

Partial updates

Hologres supports partial column updates based on the primary key using INSERT ON CONFLICT. Fixed Plan supports partial updates when:

• The columns in the INSERT clause correspond one-to-one with the columns in the DO UPDATE SET clause (same count, same order).

• The update uses col = excluded.col format.

Conditional upsert

To handle out-of-order input for rows with the same primary key, Fixed Plan supports conditional INSERT or UPDATE (similar to HBase CheckAndPut) when:

• The WHERE clause in ON CONFLICT contains only a single non-PK field.

• The comparison operator is one of: =, <>, >, >=, <, <=, IS NULL, IS NOT NULL.

• The coalesce function is supported on the non-PK field.

For multi-record conditional upserts, set hg_experimental_enable_fixed_dispatcher_for_multi_values = on.

BEGIN;
CREATE TABLE test_check_and_insert (
    pk INT,
    col INT,
    scn INT,
    PRIMARY KEY (pk)
);
COMMIT;

-- Qualifies: compare existing column value against a constant
INSERT INTO test_check_and_insert AS old VALUES (1, 1, 1)
ON CONFLICT (pk)
DO UPDATE SET col = excluded.col, scn = excluded.scn
WHERE old.scn > 0;

-- Qualifies: compare existing value against the incoming value
INSERT INTO test_check_and_insert AS old VALUES (1, 1, 1)
ON CONFLICT (pk)
DO UPDATE SET col = excluded.col, scn = excluded.scn
WHERE old.scn > excluded.scn;

-- Qualifies: handle potential nulls with coalesce
INSERT INTO test_check_and_insert AS old VALUES (1, 1, 1)
ON CONFLICT (pk)
DO UPDATE SET col = excluded.col, scn = excluded.scn
WHERE coalesce(old.scn, 3) > excluded.scn;

-- Qualifies: multi-record conditional upsert
SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
INSERT INTO test_check_and_insert AS old VALUES (1, 1, 1), (2, 3, 4)
ON CONFLICT (pk)
DO UPDATE SET col = excluded.col, scn = excluded.scn
WHERE old.scn > 3;

-- Qualifies: unnest syntax also supported
INSERT INTO test_check_and_insert AS old
SELECT unnest(ARRAY[5, 6, 7]::int[]),
       unnest(ARRAY[1, 1, 1]::int[]),
       unnest(ARRAY[1, 1, 1]::int[])
ON CONFLICT (pk)
DO UPDATE SET col = excluded.col, scn = excluded.scn
WHERE old.scn > 3;

Conditions

UPDATE statements qualify for Fixed Plan when all of the following apply:

• The target is an internal table (not a foreign table).

• For partitioned tables, the target is a child table (not the parent table).

• The table has a primary key.

• The SET clause updates only non-PK columns.

• The WHERE clause contains all and only the PK columns. In V1.3 and later, the last field in the WHERE clause can be a non-PK field, with support for =, <>, >, >=, <, <=, IS NULL, IS NOT NULL, and coalesce.

• Each PK column has at most one filter condition. Duplicate identical conditions count as one.

To update multiple records at once, use pk IN (?, ?, ?) or pk = ANY(...). For example, pk1 IN (1,2) AND pk2 = ANY('{3,4}') AND pk3 = 5 targets four records: (1,3,5), (1,4,5), (2,3,5), and (2,4,5).

Starting from V1.3.25, the hg_experimental_enable_fixed_dispatcher_for_update parameter is deprecated. UPDATE statements that meet the conditions use Fixed Plan by default. To update multiple records, set hg_experimental_enable_fixed_dispatcher_for_multi_values = on.

BEGIN;
CREATE TABLE test_update (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
COMMIT;

-- Qualifies
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1, col2 = 2 WHERE pk1 = 3 AND pk2 = 4;

-- Qualifies
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1 WHERE pk1 = 3 AND pk2 = 4;

-- Qualifies in V1.3+: WHERE clause includes a non-PK field
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1 WHERE pk1 = 3 AND pk2 = 4 AND col1 > 3;

-- Qualifies in V1.3+: non-PK field with coalesce
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1 WHERE pk1 = 3 AND pk2 = 4 AND coalesce(col1, 4) <> 1;

-- Qualifies: multi-record update using IN and ANY
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1, col2 = 2
WHERE pk1 IN (1, 2) AND pk2 = ANY('{3,4}');

-- Does not qualify: pk1 has two different filter conditions
UPDATE test_update SET col1 = 1, col2 = 2 WHERE pk1 = 3 AND pk1 = 4;

-- Does not qualify: pk1 has conflicting conditions
UPDATE test_update SET col1 = 1, col2 = 2
WHERE pk1 IN (1, 2) AND pk1 = 1;

-- Qualifies: identical duplicate conditions are treated as one
SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
UPDATE test_update SET col1 = 1, col2 = 2
WHERE pk1 IN (1, 2) AND pk1 IN (1, 2) AND pk2 = 4;

Conditions

DELETE statements qualify for Fixed Plan when all of the following apply:

• The target is an internal table (not a foreign table).

• For partitioned tables, the target is a child table (not the parent table).

• The table has a primary key.

• The WHERE clause contains all and only the PK columns. In V1.3 and later, the last field can be a non-PK field, with support for =, <>, >, >=, <, <=, IS NULL, IS NOT NULL, and coalesce.

• Each PK column has at most one filter condition. Duplicate identical conditions count as one.

To delete multiple records at once, use pk IN (?, ?, ?) or pk = ANY(...).

Starting from V1.3.25, the hg_experimental_enable_fixed_dispatcher_for_delete parameter is deprecated. DELETE statements that meet the conditions use Fixed Plan by default. To delete multiple records, set hg_experimental_enable_fixed_dispatcher_for_multi_values = on.

BEGIN;
CREATE TABLE test_delete (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
COMMIT;

-- Qualifies
SET hg_experimental_enable_fixed_dispatcher_for_delete = ON;
DELETE FROM test_delete WHERE pk1 = 1 AND pk2 = 2;

The multi-record and conditional filter patterns follow the same rules as UPDATE. See UPDATE scenarios for examples.

Point queries

A point query specifies all primary key columns as filter conditions. Fixed Plan supports point queries on internal tables.

Conditions:

• The target is an internal table (not a foreign table).

• For partitioned tables, the target is a child table (not the parent table).

• The table has a primary key.

• The WHERE clause contains all and only the PK columns.

• Each PK column has at most one condition. Duplicate identical conditions count as one.

• If a LIMIT clause is present, the limit value must be greater than 0.

To query multiple records, use pk IN (?, ?, ?) or pk = ANY(...). For example, pk1 IN (1,2) AND pk2 = ANY('{3,4}') AND pk3 = 5 returns four records: (1,3,5), (1,4,5), (2,3,5), and (2,4,5).

BEGIN;
CREATE TABLE test_select (
    pk1 INT,
    pk2 INT,
    col1 INT,
    col2 INT,
    PRIMARY KEY (pk1, pk2)
);
CALL set_table_property('test_select', 'orientation', 'row');
COMMIT;

-- Qualifies
SELECT * FROM test_select WHERE pk1 = 1 AND pk2 = 2;

PrefixScan queries

A PrefixScan query provides only a prefix of the composite primary key as filter conditions, following left-most prefix matching. This is useful for range scans on a subset of the primary key.

Requirements:

• Set hg_experimental_enable_fixed_dispatcher_for_scan = on. Requires Hologres V1.3.35 or later.

• The table must have a distribution key, and the WHERE clause must include all distribution key columns.

• The WHERE clause must contain only a left-most prefix of the primary key. If the PK is (pk1, pk2, pk3), valid prefixes are (pk1) and (pk1, pk2).

• Starting from V1.1.48, a range condition (with both upper and lower bounds) is supported for the last PK column in the prefix.

• Only row-oriented tables and row-column hybrid tables support PrefixScan. Column-oriented tables are not supported.

• Each PK column has at most one condition. Duplicate identical conditions count as one.

• If a LIMIT clause is present, the limit value must be greater than 0.

PrefixScan returns all matching rows at once. If the result size exceeds the hg_experimental_fixed_scan_bytesize_limit parameter (default: 1,048,576 bytes / 1 MB), an error is returned: scan result size larger than fixed scan size limit. Adjust this parameter to fit your workload.

BEGIN;
CREATE TABLE test_scan (
    pk1 INT,
    pk2 INT,
    pk3 INT,
    col1 INT,
    PRIMARY KEY (pk1, pk2, pk3)
);
CALL set_table_property('test_scan', 'orientation', 'row');
CALL set_table_property('test_scan', 'distribution_key', 'pk1,pk2');
COMMIT;

INSERT INTO test_scan VALUES (1, 2, 3, 4);

-- Qualifies: all distribution keys present, pk1+pk2 is a valid prefix
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2;

-- Qualifies: multi-value IN condition
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 IN (2, 3);

-- Qualifies: ANY condition
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
SELECT * FROM test_scan WHERE pk1 = ANY('{3,4}') AND pk2 IN (2, 3);

-- Qualifies in V1.1.48+: range condition on the last prefix column
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 1 AND pk3 > 1 AND pk3 < 4;

-- Qualifies in V1.1.48+: BETWEEN on the last prefix column
SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 1 AND pk3 BETWEEN 1 AND 4;

-- Does not qualify: missing distribution key pk2
SELECT * FROM test_scan WHERE pk1 = 1;

-- Does not qualify: pk2 is not a left-most prefix without pk1
SELECT * FROM test_scan WHERE pk2 = 2;

Multi-value prefix scan behavior:

pk1 IN (1,2) AND pk2 = 3          → scans (1,3) and (2,3)
pk2 = ANY('{3,4}') AND pk1 IN (1,2) → scans (1,3), (1,4), (2,3), and (2,4)

Paging in PrefixScan

Starting from Hologres V3.2, Fixed Plan supports OFFSET ... LIMIT paging in PrefixScan queries.

By default, PrefixScan returns results in ascending order of the remaining primary key columns. To return results in descending order, set the clustering key accordingly and enable hg_experimental_enable_fixed_dispatcher_for_clustering_key_scan.

Ascending order:

CREATE TABLE test_scan (
    pk1 INT,
    pk2 INT,
    pk3 INT,
    col1 INT,
    PRIMARY KEY (pk1, pk2, pk3)
) WITH (
    orientation = 'row',
    distribution_key = 'pk1,pk2',
    clustering_key = 'pk1:asc,pk2:asc,pk3:asc'
);

INSERT INTO test_scan VALUES (1,2,3,4), (1,2,5,6), (1,2,7,8);

-- Returns rows sorted by pk3 ascending, starting from the second row
SET hg_experimental_enable_fixed_dispatcher_for_scan = on;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2 OFFSET 1 LIMIT 2;

Descending order:

CREATE TABLE test_scan (
    pk1 INT,
    pk2 INT,
    pk3 INT,
    col1 INT,
    PRIMARY KEY (pk1, pk2, pk3)
) WITH (
    orientation = 'row',
    distribution_key = 'pk1,pk2',
    clustering_key = 'pk1:asc,pk2:asc,pk3:desc'
);

INSERT INTO test_scan VALUES (1,2,3,4), (1,2,5,6), (1,2,7,8);

-- Returns rows sorted by pk3 descending
SET hg_experimental_enable_fixed_dispatcher_for_scan = on;
SET hg_experimental_enable_fixed_dispatcher_for_clustering_key_scan = on;
SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2 OFFSET 1 LIMIT 2;

Check the monitoring console

INSERT, UPDATE, and DELETE statements executed via Fixed Plan appear as the SDK type on the Real-time Import RPS panel in the Hologres console.

Run EXPLAIN

Run EXPLAIN before a statement to inspect the execution plan. If the plan contains a FixedXXXNode, Fixed Plan is active.

If no FixedXXXNode appears, the statement does not qualify. Check the following in order:

1. Table type: The target must be an internal table, not a foreign table.

2. Primary key coverage: The WHERE clause must include all PK columns (for point queries and PrefixScan).

3. Data types: No column involved in the statement can be of the MONEY or MONEY ARRAY type.

4. GUC parameters: Confirm the required GUC parameter is enabled for the operation type (for example, hg_experimental_enable_fixed_dispatcher_for_scan for PrefixScan).

5. Syntax constraints: Review the conditions listed in the relevant scenario section above.

End-to-end verification example:

-- Step 1: create a table
BEGIN;
CREATE TABLE test_verify (
    pk1 INT,
    pk2 INT,
    col1 INT,
    PRIMARY KEY (pk1, pk2)
);
CALL set_table_property('test_verify', 'orientation', 'row');
COMMIT;

-- Step 2: insert a row
INSERT INTO test_verify VALUES (1, 2, 3);

-- Step 3: run EXPLAIN on the SELECT statement
EXPLAIN SELECT * FROM test_verify WHERE pk1 = 1 AND pk2 = 2;

If the output includes a node like FixedPointQueryNode or similar FixedXXXNode, the statement is using Fixed Plan. If it shows a regular scan plan instead, verify the table type, primary key coverage, and data types.