Hologres:Accelerate SQL execution by using Fixed Plan

Related GUC parameters

• GUC List

  The following Grand Unified Configuration (GUC) parameters are used for Fixed Plan. The value for each parameter can be on or off. All parameters are enabled by default in Holo Client and take effect at the session level.

  GUC name	Scenarios	Default value	GUC change history
  hg_experimental_enable_fixed_dispatcher	Checks whether Fixed Plan is enabled for the instance. Supports Fixed Plan for single-row INSERT, UPDATE, DELETE, and PrefixScan operations to write, update, delete, and query data.	on	Not applicable.
  hg_experimental_enable_fixed_dispatcher_for_multi_values	Controls Fixed Plan writes for multi-row INSERT operations. Note Atomicity is not guaranteed. When you write multiple records at once, if no error is reported, all records are written successfully. If an error is reported for one record, it is possible that all records failed to be written, or that some were written and some were not. The error for the failed records is sent to the client application for a retry.	on	Starting from Hologres V1.3.35, this GUC supports Fixed Plan for multi-row Insert, Update, and Delete operations.
  hg_experimental_enable_fixed_dispatcher_autofill_series	Supports Fixed Plan writes to tables that contain columns of the Serial type. We recommend that you enable this parameter at the session level on the client.	off	Starting from Hologres V1.3.25, the default value of this GUC parameter is on.
  hg_experimental_enable_fixed_dispatcher_for_update	Supports Fixed Plan for UPDATE operations. We recommend that you enable this parameter at the session level on the client.	off	Starting from Hologres 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 rows, you must set set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.
  hg_experimental_enable_fixed_dispatcher_for_delete	Supports Fixed Plan for DELETE operations. We recommend that you enable this parameter at the session level on the client.	off	Starting from Hologres 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 rows, you must set set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.
  hg_experimental_enable_fixed_dispatcher_for_scan	Supports Fixed Plan for PrefixScan queries. Note A PrefixScan query uses only the first few columns of a multi-column primary key as filter conditions. Fixed Plan queries for PrefixScan on column-oriented tables are not supported.	off	We recommend that you use this parameter starting from version 1.3.35.
  hg_experimental_enable_bhclient_cache_on_session	Changes the cache mode. Two modes are available. on: Uses the cached on session mode. off: Uses the cached on fe mode. Note The differences between the cached on session and cached on fe modes are as follows. In cached on session mode, each connection has its own Writer and Reader. This mode provides higher throughput for a single connection, but the startup is slower. A startup time is required for the first read or write operation on each table. In cached on fe mode, all connections on a frontend (FE) node share a Writer and Reader. The Writer and Reader do not shut down when a connection is closed. This eliminates the startup time.	off	Not applicable.
  hg_experimental_disable_fixed_planner_conflict_pk_check	Controls whether the column in the INSERT INTO <table_name> VALUES (...) ON CONFLICT(<column>) syntax can be a non-primary key field. false: No. true: The feature is supported. Note If this GUC parameter is set to true, the column can be a non-primary key field. However, the INSERT ON CONFLICT statement is still executed based on whether the primary key is duplicated (ON CONFLICT(pk)).	false	From Hologres V1.3 to V2.1.28, the field in ON CONFLICT(<column>) must be a primary key field. Starting from Hologres V2.1.29, this GUC parameter controls whether the field in ON CONFLICT(<column>) can be a non-primary key field.

• Use GUCs

  • Check whether a GUC is enabled

    You can execute the `SHOW` command to check whether a GUC is enabled.

    SHOW <GUC_name>;

    The following example shows how to use the command.

    -- Check whether Fixed Plan is enabled at the instance level.
    SHOW hg_experimental_enable_fixed_dispatcher;

  • Enabling a GUC

    • Enable a Grand Unified Configuration (GUC) parameter at the session level

      You can use the set command to set a GUC parameter at the session level. A session-level parameter is valid only for the current session. The setting becomes invalid after the session is closed. We recommend that you add the command before the SQL statement and execute them together. The syntax is as follows.

      SET <GUC_name> = <values>;

      GUC_name specifies the name of the GUC parameter, and values specifies the value of the GUC parameter.

      Consider the following example.

      -- Allow Fixed Plan writes for multi-row insert on conflict records.
      SET hg_experimental_enable_fixed_dispatcher_for_multi_values = on;

    • Enable a GUC parameter at the database level

      You can use the alter database xx set xxx command to set a GUC parameter at the database level. This setting applies to the entire database after the command is executed. You must reconnect to the database for the setting to take effect. This setting does not apply to new databases. You must manually set the parameter again for new databases. The syntax is as follows.

      ALTER DATABASE <db_name> SET <GUC_name> = <values>;

      db_name specifies the database name, GUC_name specifies the name of the GUC parameter, and values specifies the value of the GUC parameter.

      The following provides an example.

      --Enable Fixed Plan at the DB level.
      ALTER DATABASE <db_name> SET hg_experimental_enable_fixed_dispatcher = on;

Data type requirements

• No column in the table can be of the MONEY or MONEY ARRAY type.

• The following data types are supported for columns on which you perform Data Manipulation Language (DML) operations, such as INSERT, UPDATE, and DELETE, and for columns in SELECT statements. The target columns of the SELECT statement and the columns in the `where` clause must meet the requirements.

  • BOOLEAN (alias: BOOL)

  • SMALLINT

  • INTEGER (alias: INT or INT4)

  • BIGINT (alias: INT8)

  • FLOAT (alias: FLOAT4)

  • DOUBLE PRECISION (alias: FLOAT8)

  • CHAR(n)

  • VARCHAR(n)

  • BYTEA

  • JSON and JSONB

  • TEXT (alias: VARCHAR)

  • TIMESTAMP WITH TIME ZONE (alias: TIMESTAMPTZ)

  • DATE

  • TIMESTAMP

  • DECIMAL (alias: NUMERIC)

  • ROARINGBITMAP

  • TIME (supported in Hologres V2.2 and later)

  • TIMETZ (supported in Hologres V2.2 and later)

  • Array types

    • boolean[]

    • smallint[]

    • int4[]

    • int8[]

    • float4[]

    • float8[]

    • char(n)[]

    • varchar(n)[]

    • text[]

INSERT scenarios

Starting in Hologres V3.2, the `RETURNING` clause is supported when you use Fixed Plan to run `INSERT` statements on tables with primary keys.

• INSERT statement

  Fixed Plan supports the following INSERT clauses.

  -- Write a single row.
  INSERT INTO TABLE(col1,col2,col3..) VALUES(?,?,?..) ON conflict xxx;
  -- Write multiple rows.
  INSERT INTO TABLE(col1,col2,col3..) VALUES(?,?,?..),(?,?,?..) ON conflict xxx;

  Note

  • The INSERT command writes data to internal tables, but not to foreign tables.

  • Hologres supports writing to partitioned tables using the INSERT command. In Hologres V1.3 and later, you can also write to partitioned parent tables.

  • Fixed Plan does not support the RETURNING keyword in INSERT statements.

• Single-row INSERT ON CONFLICT

  • The following scenarios are supported.

    • Clauses without ON CONFLICT are supported.

    • Clauses with ON CONFLICT DO NOTHING are supported.

    • Clauses with ON CONFLICT DO UPDATE are supported. You must update all non-primary key (PK) columns that are part of the INSERT statement. You can choose whether to update the PK columns. Updates must use the col = excluded.col format. In Hologres V1.3 and later, you can update some non-PK columns, but you must still use the col = excluded.col format.

  • The following example shows how to use the statement.

    BEGIN;
    CREATE TABLE test_insert_oneline (
        pk1 INT,
        pk2 INT,
        col1 INT,
        col2 INT,
        PRIMARY KEY (pk1, pk2)
    );
    COMMIT;
    
    --Update all non-PK columns. Fixed Plan can be used.
    INSERT INTO test_insert_oneline
        VALUES (1, 2, 3, 4)
    ON CONFLICT (pk1, pk2)
        DO UPDATE SET
            col1 = excluded.col1, col2 = excluded.col2;
    
    --Update all columns (including PK and non-PK columns). Fixed Plan can be used.
    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;
    
    --You must update all non-PK columns to be inserted. This example does not include col2. Fixed Plan is supported only in Hologres V1.3 and later.
    INSERT INTO test_insert_oneline
        VALUES (1, 2, 3, 4)
    ON CONFLICT (pk1, pk2)
        DO UPDATE SET
            col1 = excluded.col1;
    
    --The update must be in the `set col = excluded.col` format. Therefore, Fixed Plan cannot be used.
    INSERT INTO test_insert_oneline
        VALUES (1, 2, 3, 4)
    ON CONFLICT (pk1, pk2)
        DO UPDATE SET
            col1 = excluded.col1, col2 = 5;

• Multi-row INSERT ON CONFLICT

  • The clause for a multi-row INSERT ON CONFLICT is as follows.

    SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
    
    INSERT INTO TABLE (col1, col2, col3..)
        VALUES (?, ?, ?..), (?, ?, ?..)
    ON CONFLICT xxx;

    • You must set the GUC parameter: hg_experimental_enable_fixed_dispatcher_for_multi_values = on;. This parameter is on by default in Hologres 1.3.35 and later.

    • Atomicity is not guaranteed. If no error is reported when you write multiple rows at once, all rows are written successfully. If an error is reported, it is possible that all rows failed to be written or that some were written and some were not.

  • Another way to write multiple rows is as follows.

    SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
    
    INSERT INTO TABLE selectunnest (ARRAY[TRUE, FALSE, TRUE]::bool[]), unnest(ARRAY[1, 2, 3]::int4[]), unnest(ARRAY[1.11, 2.222, 3]::float4[])
    ON CONFLICT xxx;

    • You must set the GUC parameter: hg_experimental_enable_fixed_dispatcher_for_multi_values=on;.

    • The columns to be written cannot be an array type.

    • The ARRAY in `unnest` must be explicitly cast to the array type of the corresponding column.

    The following shows an example.

    BEGIN;
    CREATE TABLE test_insert_multiline (
        pk1 int8,
        col1 float4,
        PRIMARY KEY (pk1)
    );
    COMMIT;
    
    --Fixed Plan is supported.
    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;
    
    --The ARRAY in unnest is not explicitly cast. Fixed Plan is not supported.
    INSERT INTO test_insert_multiline
    SELECT
        unnest(ARRAY[1, 2, 3]),
        unnest(ARRAY[1.11, 2.222, 3])
    ON CONFLICT
        DO NOTHING;
    
    --The first column is int8, so it should be cast to int8[]. This example uses int4[]. Therefore, Fixed Plan is not supported.
    INSERT INTO test_insert_multiline
    SELECT
        unnest(ARRAY[1, 2, 3]::int4[]),
        unnest(ARRAY[1.11, 2.222, 3]::float4[])
    ON CONFLICT
        DO NOTHING;

• Partial update scenarios

  Hologres lets you update some columns of a table based on the primary key. Fixed Plan also supports partial updates if the following conditions are met.

  • The columns in the INSERT statement must match the columns in the UPDATE statement in number and order.

  • Updates must use the col = excluded.col format.

• Upsert with conditional checks

  To handle out-of-order input data for rows with the same PK and to support an interface similar to HBase CheckAndPut, Hologres allows INSERT or UPDATE statements with conditional checks to use Fixed Plan if the following conditions are met.

  • This is supported when you insert a single row of data. To insert multiple rows of data, you must set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_for_multi_values=on;.

  • The WHERE clause can contain only a single non-PK field. The comparison operator must be one of the following: =, <>, >, >=, <, <=, IS NULL, IS NOT NULL. You can use the coalesce function on this non-PK field.

  The following example shows how to use the statement.

  BEGIN;
  CREATE TABLE test_check_and_insert (
      pk INT,
      col INT,
      scn INT,
      PRIMARY KEY (pk)
  );
  COMMIT;
  
  --Fixed Plan is supported.
  --Compare the existing value in a column with 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;
  
  --Compare the existing value in a column with the value to be written.
  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;
  
  --If the existing value might be null, use 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) > 2;
  
  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;
  
  --Fixed Plan is supported.
  SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
  
  --Compare the existing value in a column with a constant.
  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;
  
  --The unnest syntax is 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;

• Default column

  The following conditions must be met to use Fixed Plan on tables that contain a column with a default value.

  • This is supported when you insert a single row of data. To insert multiple rows of data, your Hologres instance must be V1.1.36 or later. If your instance is an earlier version, you must upgrade it. You must also set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_for_multi_values=on; .

  • Hologres V1.3 and later versions support Fixed Plan for the Insert on conflict clause on tables with a DEFAULT column. Earlier versions do not support Fixed Plan for the Insert on conflict clause on tables with a DEFAULT column.

  Consider the following example.

  BEGIN;
  CREATE TABLE test_insert_default (
      pk1 INT,
      col1 INT DEFAULT 99,
      PRIMARY KEY (pk1)
  );
  COMMIT;
  
  --Fixed Plan is supported.
  INSERT INTO test_insert_default (pk1)
      VALUES (1);
  
  --Supported in V1.1.36 and later.
  SET hg_experimental_enable_fixed_dispatcher_for_multi_values = ON;
  
  INSERT INTO test_insert_default (pk1)
      VALUES (1), (2), (3);

• Tables with SERIAL columns

  The following conditions must be met to use Fixed Plan for single-row or multi-row writes to a table with an auto-incrementing SERIAL column.

  • You must set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_autofill_series=on;. Starting in Hologres V1.3.25, this GUC parameter is on by default.

  • To insert multiple rows of data, you must also set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_for_multi_values=on;.

  The following shows an example.

  BEGIN;
  CREATE TABLE test_insert_serial (
      pk1 INT,
      col1 SERIAL,
      PRIMARY KEY (pk1)
  );
  COMMIT;
  
  --Fixed Plan is supported.
  SET hg_experimental_enable_fixed_dispatcher_autofill_series = ON;
  
  INSERT INTO test_insert_serial (pk1)
      VALUES (1);
  
  --Fixed Plan is supported.
  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);

UPDATE scenarios

• UPDATE statement

  The following UPDATE clause can use Fixed Plan.

  SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
  
  UPDATE TABLE SET col1 = ?,col2 = ? WHERE pk1 = ? AND pk2 = ?;

• UPDATE scenarios

  The following conditions must be met to use Fixed Plan in UPDATE scenarios.

  • You can update internal tables, but not foreign tables. You can update child partitioned tables, but not parent partitioned tables. The table must have a primary key (PK).

  • You must set the GUC parameter: hg_experimental_enable_fixed_dispatcher_for_update=on;. Starting in Hologres V1.3.25, this parameter is deprecated. UPDATE statements that meet the conditions use Fixed Plan by default. To update multiple rows, you must set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.

  • The columns in the SET clause cannot be primary keys (PKs).

  • The `WHERE` clause must contain all and only the PKs.

  • You can use pk IN (?,?,?) or pk = ANY() to modify multiple rows at once. For example, pk1 IN (1,2) and pk2 = ANY('{3,4}') and pk3 = 5 modifies four rows: (1,3,5), (1,4,5), (2,3,5), and (2,4,5).

  • Each column in the `WHERE` clause can have only one condition. Identical conditions are treated as one.

  The following provides an example.

  BEGIN;
  CREATE TABLE test_update (
      pk1 INT,
      pk2 INT,
      col1 INT,
      col2 INT,
      PRIMARY KEY (pk1, pk2)
  );
  COMMIT;
  
  --Fixed Plan is supported.
  SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
  
  UPDATE
      test_update
  SET
      col1 = 1,
      col2 = 2
  WHERE
      pk1 = 3
      AND pk2 = 4;
  
  --Fixed Plan is supported.
  SET hg_experimental_enable_fixed_dispatcher_for_update = ON;
  
  UPDATE
      test_update
  SET
      col1 = 1
  WHERE
      pk1 = 3
      AND pk2 = 4;
  
  --Fixed Plan is supported.
  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}');
  
  --pk1 has multiple filter conditions. Fixed Plan is not supported.
  UPDATE
      test_update
  SET
      col1 = 1,
      col2 = 2
  WHERE
      pk1 = 3
      AND pk1 = 4;
  
  --pk1 has multiple filter conditions. Fixed Plan is not supported.
  UPDATE
      test_update
  SET
      col1 = 1,
      col2 = 2
  WHERE
      pk1 IN (1, 2)
      AND pk1 = 1;
  
  --pk1 has multiple filter conditions, but the conditions are identical. Fixed Plan is supported.
  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;

DELETE scenarios

• DELETE statement

  The following DELETE clause can use Fixed Plan.

  SET hg_experimental_enable_fixed_dispatcher_for_delete = ON;
  
  DELETE FROM TABLE
  WHERE pk1 = ?
      AND pk2 = ?
      AND pk3 = ?;

• DELETE scenarios

  The following conditions must be met to use Fixed Plan in DELETE scenarios.

  • You can delete from internal tables, but not from foreign tables. You can delete from child partitioned tables, but not from parent partitioned tables. The table must have a primary key (PK).

  • You must set the GUC parameter: hg_experimental_enable_fixed_dispatcher_for_delete=on;. Starting in Hologres V1.3.25, this parameter is deprecated. DELETE statements that meet the conditions use Fixed Plan by default. To delete multiple rows, you must set the GUC parameter: set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.

  • The WHERE clause must contain all and only the PKs. Starting in Hologres V1.3, the last field in the WHERE filter condition can be a non-PK field. This non-PK field supports the =, <>, >, >=, <, <=, IS NULL, and IS NOT NULL comparison operators and the coalesce function.

  • You can use pk IN (?,?,?) or pk = ANY() to delete multiple rows at once. For example, pk1 IN (1,2) and pk2 = ANY('{3,4}') and pk3 = 5 deletes four rows: (1,3,5), (1,4,5), (2,3,5), and (2,4,5).

  • Each column can have only one condition. Identical conditions are treated as one.

  The following provides an example.

  BEGIN;
  CREATE TABLE test_delete (
      pk1 INT,
      pk2 INT,
      col1 INT,
      col2 INT,
      PRIMARY KEY (pk1, pk2)
  );
  COMMIT;
  
  --Fixed Plan is supported. More scenarios are consistent with the UPDATE examples.
  SET hg_experimental_enable_fixed_dispatcher_for_delete = ON;
  
  DELETE FROM test_delete
  WHERE pk1 = 1
      AND pk2 = 2;
  

SELECT scenarios

• SELECT statement

  The following SELECT clause can use Fixed Plan.

  SELECT
      col1,
      col2,
      col3,
  ...
  FROM
      TABLE
  WHERE
      pk1 = ?
      AND pk2 = ?
      AND pk3 = ?;
  

  • You can select from internal tables, but not from foreign tables.

  • You can select from child partitioned tables, but not from parent partitioned tables.

  • The table must have a primary key (PK).

• Point query (key-value) scenarios

  The following conditions apply to point query scenarios.

  • The `WHERE` clause must contain all and only the PKs.

  • You can use pk IN (?,?,?) or pk = ANY() to query multiple rows at once. For example, pk1 IN (1,2) and pk2 = ANY('{3,4}') and pk3 = 5 queries four rows: (1,3,5), (1,4,5), (2,3,5), and (2,4,5).

  • Each column can have only one condition. Identical conditions are treated as one.

  • If a LIMIT clause is used, its value must be greater than 0.

  Here is an example.

  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;
  
  --Fixed Plan is supported.
  SELECT * FROM test_select WHERE pk1 = 1 AND pk2 = 2;

• PrefixScan scenarios

  • PrefixScan scenario clause

    A PrefixScan scenario occurs when a table has multiple primary keys, and the query uses only the first few primary keys based on the left-match principle. The query clause is as follows.

    SET hg_experimental_enable_fixed_dispatcher_for_scan = on;
    SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 = ?;
    SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 < ?;--Starting from V1.1.48, the last PK column can have a range condition.
    SELECT col1,col2,col3,... FROM TABLE WHERE pk1 = ? AND pk2 BETWEEN ? AND ?;--Starting from V1.1.48, the last PK column can have a range condition.                                

  • Use PrefixScan

    The following conditions must be met to use PrefixScan.

    • You must set the GUC parameter: hg_experimental_enable_fixed_dispatcher_for_scan=on;. Your Hologres instance must be V1.3.35 or later.

    • The table must have a Distribution Key, and the WHERE clause must include all Distribution Keys.

    • The WHERE clause must contain only the prefix of the PK. Starting in V1.1.48, PrefixScan supports setting a range for the last primary key column, including an upper and lower bound.

      Note

      Prefix definition: If the PK is (pk1,pk2,pk3), then the prefixes are (pk1),(pk1,pk2).

    • Only row-oriented tables, including row-column hybrid tables, support PrefixScan.

    • Each column can have only one condition. Identical conditions are treated as one.

    • If a `LIMIT` clause is used, its value must be greater than 0.

    Note

    PrefixScan returns all result rows at once. If the byte size of the result is larger than hg_experimental_fixed_scan_bytesize_limit, an error is reported: "scan result size larger than fixed scan size limit". You can set the hg_experimental_fixed_scan_bytesize_limit parameter to a value that suits your scenario. The default value is 1,048,576 bytes (1 MB).

    For example, if the table PK is (pk1,pk2,pk3,pk4) and the Distribution Key is pk1,pk3.

    BEGIN;
    CREATE TABLE test_select_prefix (
        pk1 INT,
        pk2 INT,
        pk3 INT,
        pk4 INT,
        PRIMARY KEY (pk1, pk2, pk3, pk4)
    );
    CALL set_table_property ('test_select_prefix', 'orientation', 'row');
    CALL set_table_property ('test_select_prefix', 'distribution_key', 'pk1,pk3');
    COMMIT;
    
    --Not all distribution keys are included. Fixed Plan cannot be used.
    SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk2 = ?;
    --This is not a prefix of the PK. Fixed Plan cannot be used.
    SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk3 = ?;
    
    --Fixed Plan can be used.
    SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
    
    SELECT * FROM test_select_prefix WHERE pk1 = ? AND pk2 = ? AND pk3 = ?;
    

    You can use pk IN (?,?,?) or pk = ANY() to query multiple rows at once. The command is as follows.

    pk1 IN (1,2) AND pk2 = 3 <=> scans two groups: (1,3) and (2,3)
    pk2 =any('{3,4}') AND pk1 IN (1,2) <=> scans four groups: (1,3), (1,4), (2,3), and (2,4)

  • Usage example

    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);
    
    --Fixed Plan is supported.
    SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
    
    SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2;
    
    --Fixed Plan is supported.
    SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
    
    SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 IN (2, 3);
    
    --Fixed Plan is supported.
    SET hg_experimental_enable_fixed_dispatcher_for_scan = ON;
    
    SELECT * FROM test_scan WHERE pk1 = ANY ('{3,4}') AND pk2 IN (2, 3);
    
    --Fixed Plan is supported. The last PK column has a range condition. Supported in V1.1.48 and later.
    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;
    
    --Fixed Plan is supported. The last PK column has a range condition. Supported in V1.1.48 and later.
    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;
    
    --Not all distribution keys are included. Fixed Plan is not supported.
    SELECT * FROM test_scan WHERE pk1 = 1;
    
    --Does not match the primary key prefix. Fixed Plan is not supported.
    SELECT * FROM test_scan WHERE pk2 = 2;

• Paged query scenarios

  Starting in Hologres V3.2, Fixed Plan supports paged queries in PrefixScan scenarios that are based on a partial primary key. The following examples show how this works:

  Note

  • By default, the results of a PrefixScan are returned in ascending order of the primary key. For example, in the following SQL statement, a PrefixScan based on `pk1` and `pk2` returns results sorted in ascending order of `pk3`.

  • To specify a custom sort order, you can set the order of the corresponding columns in the Clustering Key and specify the relevant GUC when you run the query. The results are then sorted by the Clustering Key. The fields of the Clustering Key must be identical to the primary key. For example, to return results in descending order, you can manually set the last item of the Clustering Key to pk3:desc.

  • Paginated results in ascending order

    -- Create a table.
    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'
    );
    
    -- Write data.
    INSERT INTO test_scan VALUES (1,2,3,4),(1,2,5,6),(1,2,7,8);
    
    -- Supports offset + limit. Based on PrefixScan, returns a specific number of rows starting from a specified row.
    SET hg_experimental_enable_fixed_dispatcher_for_scan = on;
    -- By default, results are sorted in ascending order of pk3.
    SELECT * FROM test_scan WHERE pk1 = 1 AND pk2 = 2 OFFSET 1 limit 2;

  • Paginated results in descending order

    -- Create a table.
    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'
    );
    
    -- Write data.
    INSERT INTO test_scan VALUES (1,2,3,4),(1,2,5,6),(1,2,7,8);
    
    -- Enable the following two GUCs. The results are sorted in descending order of pk3.
    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;

COPY scenarios

COPY table_name (column0, column1, column2)
FROM
    STDIN WITH (
        format BINARY,
        stream_mode TRUE,
        on_conflict UPDATE);

Fixed Plan support for expressions

Starting in Hologres V3.2, Fixed Plan supports SQL statements that contain expressions. For more information about PostgreSQL expressions, see PostgreSQL Expressions. Expressions are supported in the following scenarios:

• INSERT statement:

  • VALUES clause.

  • INSERT ON CONFLICT DO UPDATE clause.

  • Filter conditions in the INSERT ON CONFLICT WHERE clause.

  • RETURNING clause.

• SELECT statement

  Fields in the SELECT list.

Verify Fixed Plan

• SQL statements that modify data and are executed using Fixed Plan, including INSERT, UPDATE, and DELETE operations, are displayed as the SDK type on the Real-time Import RPS panel in the console. We recommend that you optimize real-time write operations such as INSERT, UPDATE, and DELETE to use Fixed Plan to improve data update efficiency. The following figure shows an example of the monitoring metrics.

• You can view the SQL execution plan using `EXPLAIN `. If the returned execution plan contains FixedXXXNode, Fixed Plan was triggered, as shown in the following figure. If the generated execution plan does not contain FixedXXXNode, check whether the conditions described in the preceding sections are met.

Performance tuning

FAQ

• Question 1: The connection fails with the error: role/database does not exist.

  • Cause: The user or database does not exist.

  • Solution: Check the connection information and enter the correct username or database name.

    You can log on to the Hologres Management Console, find the target instance, click Manage in the Actions column, and then click Database Management. You can find and confirm the username or database name on the Users and Database Authorization pages.

• Question 2: An error occurs during data writing: the requested table name: xxx (id: xx, version: xx) mismatches the version of the table (id: xx, version: xx) from server.

  • Cause: The table metadata changed during the data writing process. For example, a column was added. This caused the table version to change.

  • Solution: Re-establish the connection. Fixed Plan retrieves the new table metadata and performs the write operation.