The fixed plan feature is uniquely developed for Hologres query engines to optimize the execution of SQL statements. This topic describes the Grand Unified Configuration (GUC) parameters related to the fixed plan feature and the requirements for SQL statements that can be processed by using fixed plans.

Background information

Normally, SQL statements are processed by a series of Hologres components in sequence, including the optimizer, coordinator, query engine, and storage engine. By comparison, a fixed plan shortens the execution path of an SQL statement by skipping some components. In a fixed plan, a fixed frontend (FE) distributes an SQL statement to a fixed query engine. This significantly improves execution performance and makes high-throughput real-time data writes and a large number of concurrent queries possible. For more information about the fixed plan feature, see Architecture.

GUC parameters related to the fixed plan feature

  • GUC parameters

    The following table describes the GUC parameters related to the fixed plan feature. Valid values of all the GUC parameters are on and off. By default, all the GUC parameters are set to on in Holo Client and take effect at the session level.

    Parameter Description Default value
    hg_experimental_enable_fixed_dispatcher Specifies whether to enable the fixed plan feature for the Hologres instance. on
    hg_experimental_enable_fixed_dispatcher_for_multi_values Specifies whether to allow the execution of an INSERT ON CONFLICT statement that writes multiple rows by using a fixed plan. We recommend that you enable this feature at the session level on your client.
    Note The atomicity of the write operation is not guaranteed. If no error is reported, all rows are written. If an error is reported, only some or no rows are written. The system reports an error that includes the information about the rows that fail to be written to the upper-layer application. The upper-layer application tries to write these rows again.
    off
    hg_experimental_enable_fixed_dispatcher_autofill_series Specifies whether to allow the execution of a statement that involves a column of the SERIAL data type by using a fixed plan. We recommend that you enable this feature at the session level on your client. off
    hg_experimental_enable_fixed_dispatcher_for_update Specifies whether to allow the execution of an UPDATE statement by using a fixed plan. We recommend that you enable this feature at the session level on your client. off
    hg_experimental_enable_fixed_dispatcher_for_delete Specifies whether to allow the execution of a DELETE statement by using a fixed plan. We recommend that you enable this feature at the session level on your client. off
    hg_experimental_enable_fixed_dispatcher_for_scan Specifies whether to allow the execution of an SQL prefix query by using a fixed plan.
    Note You are performing an SQL prefix query when you specify only the first several columns of the primary key of a table in the query statement.
    off
    hg_experimental_enable_bhclient_cache_on_session Specifies whether to use the cached on session mode or cached on FE mode to cache data. Valid values:
    • on: uses the cached on session mode.
    • off: uses the cached on FE mode.
    Note The cached on session and cached on FE modes have the following differences:
    • In cached on session mode, each session is provided with a reader and a writer. This mode provides higher throughput but statements take a longer time to execute. This is because the reader and the writer take time to start when you read or write a table for the first time.
    • In cached on FE mode, all sessions on an FE share a reader and a writer. If a session is closed, the reader and the writer are still ready to be used. This saves the startup time for the reader and the writer.
    off
  • Use the GUC parameters
    • Check the setting of a GUC parameter
      You can execute the SHOW statement to check the setting of a GUC parameter. Syntax:
      show <GUC_name>;
      Example:
      -- Check whether the fixed plan feature is enabled for the Hologres instance.
      show hg_experimental_enable_fixed_dispatcher;
    • Set a GUC parameter
      • Set a GUC parameter at the session level
        You can execute the SET statement to set a GUC parameter at the session level. If you set GUC parameters at the session level, the settings take effect only in the current session. After the session is closed, the settings become invalid. We recommend that you add the statements for setting GUC parameters before the SQL statements to execute. Syntax:
        set <GUC_name> = <values>;
        Replace GUC_name with the name of the GUC parameter and values with the value to be set for the GUC parameter.
        Example:
        -- Allow the execution of an INSERT ON CONFLICT statement that writes multiple rows by using a fixed plan.
        set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
      • Set a GUC parameter at the database level
        You can execute the ALTER DATABASE xx SET xxx statement to set GUC parameters at the database level. This way, the settings of the GUC parameters take effect for the specified database and remain valid after you close the current session and establish another session. You cannot set the GUC parameters when you create a database. You must manually set the GUC parameters for a database after it is created. Syntax:
        alter database <db_name> set <GUC_name> = <values>;
        The db_name parameter specifies the name of the database. The GUC_name parameter specifies the name of the GUC parameter, and the values parameter specifies the value of the GUC parameter.
        Example:
        -- Enable the fixed plan feature for a specific database.
        alter database <db_name> set
         hg_experimental_enable_fixed_dispatcher =on;

Requirements on data types

  • Each column cannot contain data of the Money type or MONEY arrays.
  • The following data types are supported for columns on which you want to perform DML operations, including the INSERT, UPDATE, and DELETE operations, or execute the SELECT statement. Both the columns to be queried and those specified in the WHERE clause in a SELECT statement must be of the data types in the following list.
    • 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
    • Array types
      • boolean[]
      • smallint[]
      • int4[]
      • int8[]
      • float4[]
      • float8[]
      • char(n)[]
      • varchar(n)[]
      • text[]

INSERT statements

  • Syntax
    To use fixed plans, execute INSERT statements in the following syntax:
    -- 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
    • You can execute INSERT statements to write data to internal tables but not to foreign tables.
    • You can execute INSERT statements to write data to partitioned tables. In Hologres V1.3 and later, you can execute INSERT statements to write data to parent partitioned tables.
  • Execute INSERT ON CONFLICT statements to write a single row
    • The following types of INSERT statements are supported:
      • An INSERT statement without the ON CONFLICT clause.
      • An INSERT statement that contains the ON CONFLICT DO NOTHING clause.
      • An INSERT statement that contains the ON CONFLICT DO UPDATE clause. All columns except for the primary key columns must be updated. You can specify whether to update the primary key columns as needed. You must specify columns to be updated in the col = excluded.col format. In Hologres V1.3 and later, you can update only some of the columns that are not the primary key columns. However, you can also specify columns to be updated in the col = excluded.col format.
    • Example:
      begin;
      create table test_insert_oneline(
        pk1 int,
        pk2 int,
        col1 int,
        col2 int,
        primary key(pk1, pk2)
      );
      commit;
      
      -- Update all columns except for the primary key columns. In this example, a 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 the primary key columns. In this example, a 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;
      
      -- To use a fixed plan, you must update all columns except for the primary key columns. In this example, the col2 column is not specified in the statement. In this case, a fixed plan can be used 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;
      
      -- To use a fixed plan, you must specify columns to be updated in the col = excluded.col format in a SET clause. In this example, a 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;
  • Execute INSERT ON CONFLICT statements to write multiple rows
    • Syntax:
      set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
      insert into table(col1,col2,col3..) values(?,?,?..),(?,?,?..) on conflict xxx;
      • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter must be set to on.
      • The atomicity of the write operation is not guaranteed. If no error is reported, all rows are written. If an error is reported, all rows are not written or only specific rows are written.
    • The following syntax can also be used to write multiple rows:
      set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
      insert into table select
      unnest(ARRAY[true, false, true]::bool[]),
      unnest(ARRAY[1,2,3]::int4[]),
      unnest(ARRAY[1.11,2.222,3]::float4[]) on conflict xxx;
      • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter must be set to on.
      • Data of the ARRAY type cannot be written to the table.
      • In the UNNEST clause, the specified arrays must be explicitly converted to the array types of the corresponding columns.
      Example:
      begin;
      create table test_insert_multiline(
        pk1 int8,
        col1 float4,
        primary key(pk1)
      );
      commit;
      
      -- In this example, a fixed plan can be used.
      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;
      
      -- In this example, the specified array in the UNNEST clause is not explicitly converted to the array type of the corresponding column Therefore, a fixed plan cannot be used.
      insert into test_insert_multiline select unnest(ARRAY[1,2,3]), unnest(ARRAY[1.11,2.222,3]) on conflict do nothing;
      
      -- The data type of the first column is INT8. Therefore, the array must be explicitly converted to the INT8[] type. In this example, the array is explicitly converted to the INT4[] type. Therefore, a fixed plan cannot be used.
      insert into test_insert_multiline select unnest(ARRAY[1,2,3]::int4[]), unnest(ARRAY[1.11,2.222,3]::float4[]) on conflict do nothing;
  • Update specific columns in a table
    Hologres allows you to update specific columns in a table based on the primary key. If the following requirements are met, you can use fixed plans to update specific columns in a table:
    • The columns to be written map the columns to be updated in both quantity and sequence.
    • The columns to be updated are specified in the col = excluded.col format.
  • Write data to a table that contains a column with the DEFAULT constraint
    You can use fixed plans to write data to a table that contains a column with the DEFAULT constraint if the following requirements are met:
    • The statement to be executed writes a single row.
    • If the statement writes multiple rows to the table, the version of the instance is Hologres V1.1.36 or later. Otherwise, you must update the instance.
    • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter is set to on.
    • In Hologres V1.3 and later, you can use fixed plans to execute INSERT ON CONFLICT statements on a table that contains a column with the DEFAULT constraint. In a version earlier than Hologres V1.3, you cannot use fixed plans to execute INSERT ON CONFLICT statements on a table that contains a column with the DEFAULT constraint.
    Example:
    begin;
    create table test_insert_default(
      pk1 int,
      col1 int default 99,
      primary key(pk1)
    );
    commit;
    
    -- In this example, a fixed plan can be used.
    insert into test_insert_default(pk1) values(1);
    
    -- You must use an instance of Hologres V1.1.36 or later.
    set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
    insert into test_insert_default(pk1) values(1),(2),(3);
  • Write data to a table that contains a column of the SERIAL data type
    If the following requirements are met, you can use fixed plans to write a single row or multiple rows to a table that contains a column of the SERIAL data type:
    • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter is set to on.
    • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter is set to on if you want to write multiple rows.
    • The statement to be executed is not an INSERT ON CONFLICT statement.
    Example:
    begin;
    create table test_insert_serial(
      pk1 int,
      col1 serial,
      primary key(pk1)
    );
    commit;
    
    -- In this example, a fixed plan can be used.
    set hg_experimental_enable_fixed_dispatcher_autofill_series =on;
    insert into test_insert_serial (pk1) values(1);
    
    -- In this example, a fixed plan can be used.
    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 statements

  • Syntax
    To use fixed plans, execute UPDATE statements in the following syntax:
    set hg_experimental_enable_fixed_dispatcher_for_update =on;
    update table set col1 = ?, col2 = ? where pk1 = ? and pk2 = ?;
    • The hg_experimental_enable_fixed_dispatcher_for_update GUC parameter must be set to on.
    • You can update data in an internal table but not in a foreign table.
    • You can update data in child partitioned tables but not in a parent partitioned table.
    • The specified table must have a primary key.
  • Usage notes
    When you execute UPDATE statements by using fixed plans, take note of the following items:
    • You cannot specify primary key columns in the SET clause.
    • You must specify all primary key columns in the WHERE clause.
    • You can specify primary key columns in the pk in (?,?,?) or pk = ANY() format to update multiple rows at a time. Example: pk1 in (1,2) and pk2 = any('{3,4}') and pk3 = 5. In this case, the four rows (1,3,5), (1,4,5), (2,3,5), and (2,4,5) are updated.
    • You can specify only a single condition in the WHERE clause. Duplicate conditions are considered one condition.
    Example:
    begin;
    create table test_update(
      pk1 int,
      pk2 int,
      col1 int,
      col2 int,
      primary key(pk1, pk2)
    );
    commit;
    
    -- In this example, a fixed plan can be used.
    set hg_experimental_enable_fixed_dispatcher_for_update =on;
    
    update test_update set col1 = 1, col2 = 2 where pk1 = 3 and pk2 = 4;
    
    -- In this example, a fixed plan can be used.
    set hg_experimental_enable_fixed_dispatcher_for_update =on;
    update test_update set col1 = 1 where pk1 = 3 and pk2 = 4;
    
    -- In this example, a fixed plan can be used.
    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}');
    
    -- In this example, multiple conditions are specified for the pk1 column in the WHERE clause. Therefore, a fixed plan cannot be used.
    update test_update set col1 = 1, col2 = 2 where pk1 = 3 and pk1 = 4;
    
    -- In this example, multiple conditions are specified for the pk1 column in the WHERE clause. Therefore, a fixed plan cannot be used.
    update test_update set col1 = 1, col2 = 2 where pk1 in (1,2) and pk1 = 1;
    
    -- In this example, multiple duplicate conditions are specified for the pk1 column in the WHERE clause. Therefore, a fixed plan can be used.
    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 statements

  • Syntax
    To use fixed plans, execute DELETE statements in the following syntax:
    set hg_experimental_enable_fixed_dispatcher_for_delete =on;
    delete from table where pk1 = ? and pk2 = ? and pk3 = ?;
    • The hg_experimental_enable_fixed_dispatcher_for_delete GUC parameter must be set to on.
    • You can delete data from an internal table but not from a foreign table.
    • You can delete data from child partitioned tables but not from a parent partitioned table.
    • The specified table must have a primary key.
  • Usage notes
    When you execute DELETE statements by using fixed plans, take note of the following items:
    • You must specify all primary key columns in the WHERE clause.
    • You can specify primary key columns in the pk in (?,?,?) or pk = ANY() format to delete multiple rows at a time. Example: pk1 in (1,2) and pk2 = any('{3,4}') and pk3 = 5. In this case, the four rows (1,3,5), (1,4,5), (2,3,5), and (2,4,5) are deleted.
    • You can specify only one condition in the WHERE clause. Duplicate conditions are regarded as one condition.
    Example:
    begin;
    create table test_delete(
      pk1 int,
      pk2 int,
      col1 int,
      col2 int,
      primary key(pk1, pk2)
    );
    commit;
    
    -- In this example, a fixed plan can be used. View the description of UPDATE statements for more examples.
    set hg_experimental_enable_fixed_dispatcher_for_delete =on;
    delete from test_delete where pk1 = 1 and pk2 = 2;

SELECT statements

  • Syntax
    To use fixed plans, execute SELECT statements in the following syntax:
    select col1,col2,col3,... from table where pk1 = ? and pk2 = ? and pk3 = ?;
    • You can query data in an internal table but not in a foreign table.
    • You can query data in child partitioned tables but not in a parent partitioned table.
    • The specified table must have a primary key.
  • Perform point queries
    When you perform point queries by using fixed plans, take note of the following items:
    • You must specify all primary key columns in the WHERE clause.
    • You can specify primary key columns in the pk in (?,?,?) or pk = ANY() format to query multiple rows at a time. Example: pk1 in (1,2) and pk2 = any('{3,4}') and pk3 = 5. In this case, the four rows (1,3,5), (1,4,5), (2,3,5), and (2,4,5) are queried.
    • You can specify only one condition in the WHERE clause. Duplicate conditions are regarded as one condition.
    • If you specify a limit for the return result, the value must be greater than 0.
    Example:
    begin;
    create table test_select(
      pk1 int,
      pk2 int,
      col1 int,
      col2 int,
      primary key(pk1, pk2)
    );
    commit;
    
    -- In this example, a fixed plan can be used.
    select * from test_select where pk1 = 1 and pk2 = 2;
  • Perform SQL prefix queries
    • Syntax
      If the primary key of a table contains multiple columns, you can specify only the first several primary key columns to perform prefix queries, which is realized based on the leftmost prefix matching principle. Syntax:
      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 > ? and pk 2 < ?; -- As of Hologres V1.1.48, the last primary key column can be used to specify the query range.
      select col1,col2,col3,... from table where pk1 = ? and pk2  between ? and ?; -- As of Hologres V1.1.48, the last primary key column can be used to specify the query range.
      • The hg_experimental_enable_fixed_dispatcher_for_scan GUC parameter must be set to on. The instance that you use must be of Hologres V1.1.24 or later.
        Note Prefix queries return all results at a time. If the size of the return result exceeds the limit that you specify by using the hg_experimental_fixed_scan_bytesize_limit parameter, the error message scan result size larger than fixed scan size limit is returned. You can modify the hg_experimental_fixed_scan_bytesize_limit parameter to a more appropriate value as needed. The default value is 1048576, which indicates a limit of 1 MB on the return result.
      • As of Hologres V1.1.48, the last primary key column can be used to specify the query range.
      • The specified table must have a distribution key.
    • Usage notes
      To perform prefix queries by using fixed plans, take note of the following items:
      • You must specify only the prefix columns of the primary key in the WHERE clause.
        Note Example of prefix columns: If the primary key of a table contains the pk1, pk2, and pk3 columns, the pk1 column alone or the pk1 and pk2 columns can be used as the prefix.
      • You must specify all distribution key columns in the WHERE clause.
      If the primary key of a table contains the pk1, pk2, pk3, and pk4 columns, the distribution key columns are the pk1 and pk3 columns. Example:
      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', 'distribution_key', 'pk1,pk3');
      commit;
      
      
      -- In this example, not all distribution key columns are specified in the WHERE clause. Therefore, a fixed plan cannot be used.
      select * from test_select_prefix where pk1 = ? and pk2 = ?;
      
      -- In this example, the prefix of the primary key columns is not used. Therefore, a fixed plan cannot be used.
      select * from test_select_prefix where pk1 = ? and pk3 =?;
      
      -- In this example, a 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 specify primary key columns in the pk in (?,?,?) or pk = ANY() format to query multiple rows at a time. Syntax:
      pk1 in (1,2) and pk2 = 3: queries the (1,3) and (2,3) rows.
      Pk2=any('{3,4}') and pk1 in (1,2): queries the (1,3), (1,4), (2,3), and (2,4) rows.
      • You can specify only one condition in the WHERE clause. Duplicate conditions are regarded as one condition.
      • If you specify a limit for the return result, the value must be greater than 0.
      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', 'distribution_key', 'pk1,pk2');
      commit;
      INSERT INTO test_scan values (1,2,3,4);
      
      -- In this example, a fixed plan can be used.
      set hg_experimental_enable_fixed_dispatcher_for_scan = on;
      select * from test_scan where pk1 = 1 and pk2 = 2;
      
      -- In this example, a fixed plan can be used.
      set hg_experimental_enable_fixed_dispatcher_for_scan = on;
      select * from test_scan where pk1 = 1 and pk2 in (2,3);
      
      -- In this example, a fixed plan can be used.
      set hg_experimental_enable_fixed_dispatcher_for_scan = on;
      select * from test_scan where pk1 = ANY('{3,4}') and pk2 in (2,3);
      
      -- In this example, a fixed plan can be used. The last primary key column can be used to specify the query range, which is supported by Hologres V1.1.48 or later.
      set hg_experimental_enable_fixed_dispatcher_for_scan = on;
      select * from test_scan where pk1 = 1 and pk2 > 1 and pk2 < 3;
      
      -- In this example, a fixed plan can be used. The last primary key column is used to specify the query range, which is supported by Hologres V1.1.48 or later.
      set hg_experimental_enable_fixed_dispatcher_for_scan = on;
      select * from test_scan where pk1 = 1 and pk2 between 1 and 3;
      
      -- In this example, not all distribution key columns are specified in the WHERE clause. Therefore, a fixed plan cannot be used.
      select * from test_scan where pk1 = 1;
      
      -- In this example, the prefix of the primary key columns is not used. Therefore, a fixed plan cannot be used.
      select * from test_scan where pk2 = 2;

Verify that a fixed plan is used

  • In the Hologres console, update-class statements that are executed by using fixed plans are displayed in the Real-time Import (RPS) section of the Monitoring Information page as INSERT, UPDATE, and DELETE operations performed by using SDKs. We recommend that you execute INSERT, UPDATE, and DELETE statements by using fixed plans to improve data update efficiency.
  • You can view the SQL execution plan to verify that a fixed plan is used to process an SQL statement. If the returned execution plan contains FixedXXXNode, a fixed plan is triggered to process the SQL statement, as shown in the following figure. If FixedXXXNode is not returned in the execution plan, check whether the requirements described in the preceding sections are met. Verify that a fixed plan is used

Optimize performance

In specific scenarios in which fixed plans are used, you can use one of the following methods to optimize performance:
  • Update the version of your instance to V1.1.49 or later. The performance of point queries performed by using fixed plans is optimized as of Hologres V1.1.49. In point queries that involve large amounts of data, the throughput is improved by more than 30%.
  • Set the number of SQL statements to be executed at a time to a multiple of 512. Practices have proven that when the number of SQL statements to be executed at a time is set to a multiple of 512, the performance is optimal.