Hologres:Accelerate the execution of SQL statements by using fixed plans

GUC parameters related to the fixed plan feature

• GUC parameters

  The following table describes the GUC parameters related to the fixed plan feature. The 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	GUC change record
  hg_experimental_enable_fixed_dispatcher	Specifies whether to enable the fixed plan feature for the Hologres instance. SQL statements that are used to write data to, update data in, delete data from, or query data in a single row can be processed by using fixed plans. Such SQL statements include INSERT, UPDATE, DELETE, and SQL prefix query.	on	N/A
  hg_experimental_enable_fixed_dispatcher_for_multi_values	Specifies whether to allow the execution of an INSERT ON CONFLICT statement by using a fixed plan to write data to multiple rows. Note The atomicity of the write operation is not guaranteed. If no error is reported, data is written to all rows. If an error is reported, no data is written to the rows, or data is written to the specified rows. The information about the rows that fail to be written is reported to the upper-layer application. The upper-layer application tries to write these rows again.	on	In Hologres V1.3.35 and later, this GUC parameter specifies to allow INSERT, UPDATE, and DELETE statements to be executed on multiple rows by using a fixed plan.
  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	In Hologres V1.3.25 and later, the default value of this GUC parameter is on.
  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	In Hologres V1.3.25 and later, this parameter is no longer required. By default, an UPDATE statement that meets conditions is processed using a fixed plan. If you want to update data in multiple rows by using the UPDATE statement, you must set the hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter to on.
  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	In Hologres V1.3.25 and later, this parameter is no longer required. By default, a DELETE statement that meets conditions is processed using a fixed plan. If you want to delete data from multiple rows by using the DELETE statement, you must set the hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter to on.
  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. Hologres does not support the execution of an SQL prefix query on a column-oriented table by using a fixed plan.	off	We recommend that you use Hologres V1.3.35 and later.
  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 per connection, but statements take a longer time to execute in this mode. This is because the reader and the writer take time to start when you read data from or write data to 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	N/A

• Use a GUC parameter

  • Check the setting of a GUC parameter

    You can execute the following SHOW statement to check the setting of a GUC parameter:

    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 following SET statement to set a GUC parameter at the session level. If you set the GUC parameter at the session level, the setting takes effect only in the current session. After the session is closed, the setting becomes invalid. We recommend that you add the statements for setting the GUC parameter before the SQL statements are executed.

      set <GUC_name> = <values>;

      The GUC_name parameter specifies the name of the GUC parameter, and the values parameter specifies the value that you want to set for the GUC parameter.

      Example:

      -- Allow the execution of an INSERT ON CONFLICT statement by using a fixed plan to write data to multiple rows.
      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 the GUC parameter at the database level. This way, the setting of the GUC parameter takes effect for the specified database and remains valid after you close the current session and establish another session. You cannot set the GUC parameter when you create a database. You must manually set the GUC parameter for a database after the database is created. Sample statement:

      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 such as INSERT, UPDATE, and DELETE or execute the SELECT statement. Both the columns to be queried and those specified in the WHERE clause of 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 data to a single row.
  insert into table(col1,col2,col3..) values(?,?,?..) on conflict xxx;
  -- Write data to multiple rows.
  insert into table(col1,col2,col3..) values(?,?,?..),(?,?,?..) on conflict xxx;

  Note

  • You can execute INSERT statements to write data to internal tables instead of 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 a parent table.

• Execute INSERT ON CONFLICT statements to write data to 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 non-primary key columns must be updated. You can specify whether to update the primary key columns based on your business requirements. You must specify columns that you want to update in the col = excluded.col format. In Hologres V1.3 and later, you can update only specified non-primary key columns. However, you can also specify columns that you want to update 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 non-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 non-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 that you want to update 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 data to 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. In Hologres V1.3.35 and later, this parameter is set to on by default.

    • The atomicity of the write operation is not guaranteed. If no error is reported, data is written to all rows. If an error is reported, no data is written to the rows, or data is written to the specified rows.

  • The following syntax can also be used to write data to 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. You can use fixed plans to update specific columns in a table if the following conditions are met:

  • The columns that you want to write data to map the columns that you want to update in both quantity and sequence.

  • The columns that you want to update are specified in the col = excluded.col format.

• Execute UPDATE or INSERT statements with conditional clauses

  To cope with the out-of-order requirements of the input data on the rows that share the same primary key and to support HBase CheckAndPut, Hologres allows you to use fixed plans to execute INSERT or UPDATE statements with conditional clauses if the following conditions are met:

  • Fixed plans can be used if you write data to a single row. If you want to write data to multiple rows, the hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter must be set to on.

  • If a WHERE clause contains only a single non-primary key field and some of the following operators are used: =, <>, >, >=, <, <=, IS NULL, and IS NOT NULL, the coalesce function can be called to process this field.

  Example:

  begin;
  create table test_check_and_insert(
    pk int,
    col int,
    scn int,
    primary key(pk)
  );
  commit;
  
  -- In this example, a fixed plan can be used.
  -- Compare the existing values in a column with constants.
  insert into test_check_and_insert as old values(1, 1, 1) on conflict (k) do update set col = excluded.col, scn = excluded.scn where old.scn > 0;
  
  -- Compare the existing values in a column with the written values.
  insert into test_check_and_insert as old values(1, 1, 1) on conflict (k) do update set col = excluded.col, scn = excluded.scn where old.scn > excluded.scn;
  
  -- If the existing value is null, the coalesce function can be used.
  insert into test_check_and_insert as old values(1, 1, 1) on conflict (k) 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 (k) do update set col = excluded.col, scn = excluded.scn where coalesce(old.scn,3) > excluded.scn;
  
  -- In this example, a fixed plan can be used.
  set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;
  -- Compare the existing values in a column with constants.
  insert into test_check_and_insert as old values(1, 1, 1),(2, 3, 4) on conflict (k) do update set col = excluded.col, scn = excluded.scn where old.scn > 3;
  
  -- The UNNEST clause is 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 (k) do update set col = excluded.col, scn = excluded.scn where old.scn > 3;

• 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 conditions are met:

  • Fixed plans can be used if a single row of data is inserted. If you want to write data to multiple rows, the version of your Hologres instance must be V1.1.36 or later. Otherwise, you must update your instance. The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter must be 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

  You can use fixed plans to write data to a single row or multiple rows of a table that contains a column of the SERIAL data type if the following conditions are met:

  • The hg_experimental_enable_fixed_dispatcher_autofill_series GUC parameter must be set to on. In Hologres V1.3.25 and later, the default value of this parameter is on.

  • The hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter must be set to on if you want to write data to multiple rows.

  • The statement that you want to execute 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 = ?;

• Limits

  When you execute UPDATE statements by using fixed plans, take note of the following items:

  • You can update data in an internal table instead of a foreign table. You can update data in child tables instead of a parent table. The specified table must have a primary key.

  • The hg_experimental_enable_fixed_dispatcher_for_update GUC parameter must be set to on. In Hologres V1.3.25 and later, this parameter is no longer required. By default, an UPDATE statement that meets conditions is processed by using a fixed plan. If you want to update data in multiple rows by using the UPDATE statement, you must set the hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter to on.

  • 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 one condition in the WHERE clause for a column. 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 = ?;

• Limits

  When you execute DELETE statements by using fixed plans, take note of the following items:

  • You can delete data in an internal table instead of a foreign table. You can delete data in child tables instead of a parent table. The specified table must have a primary key.

  • The hg_experimental_enable_fixed_dispatcher_for_delete GUC parameter must be set to on. In Hologres V1.3.25 and later, this parameter is no longer required. By default, a DELETE statement that meets conditions is processed by using a fixed plan. If you want to delete multiple data from multiple rows by using the DELETE statement, you must set the hg_experimental_enable_fixed_dispatcher_for_multi_values GUC parameter to on.

  • You must specify all primary key columns in the WHERE clause. In Hologres V1.3 and later, if a WHERE clause contains a single non-primary key field and some of the following operators are used: =, <>, >, >=, <, <=, IS NULL, and IS NOT NULL, the coalesce function can be called to process this field.

  • 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 for a column. Duplicate conditions are considered 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 instead of a foreign table.

  • You can query data in child tables instead of a parent 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 for a column. Duplicate conditions are considered one condition.

  • If you specify a limit for the return result, the value of the limit 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. The prefix queries are performed 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 < ?;-- In Hologres V1.1.48 and later, 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 ?;-- In Hologres V1.1.48 and later, the last primary key column can be used to specify the query range.
                                    

  • Limits

    To perform prefix queries by using fixed plans, take note of the following items:

    • The hg_experimental_enable_fixed_dispatcher_for_scan GUC parameter must be set to on. The version of the instance that you use must be Hologres V1.1.24 or later. We recommend that you use Hologres V1.3.35 and later.

    • Distribution keys must be specified for tables. You must specify all distribution key columns in the WHERE clause.

    • You must specify the prefix of all primary key columns in the WHERE clause. In Hologres V1.1.48 and later, the last primary key column can be used to specify the query range.

      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.

    • Prefix queries cannot be performed on column-oriented tables.

    • You can specify only one condition in the WHERE clause for a column. Duplicate conditions are considered one condition.

    • If you specify a limit for the return result, the value of the limit must be greater than 0.

    Note

    Prefix queries return all resulting rows at a time. If the size of the rows 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 set the hg_experimental_fixed_scan_bytesize_limit parameter to a more appropriate value. The default value is 1048576, which indicates a limit of 1 MB on the returned rows.

    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.

    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. Statement:

    pk1 in (1,2) and pk2 = 3 <=> Query the (1,3) and (2,3) rows.
    pk2 =any('{3,4}') and pk1 in (1,2) <=> Query the (1,3), (1,4), (2,3), and (2,4) rows.

  • Examples

    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 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;
    
    -- 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 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;
    
    -- 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;

COPY statements

COPY table_name(column0,column1,column2) FROM STDIN
WITH (
  format binary,
  stream_mode true,
  on_conflict update);

Verify that a fixed plan is used

• In the Hologres console, update 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 execute the EXPLAN statement to view the SQL execution plan and 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 contained in the execution plan, check whether the conditions described in the preceding sections are met.

Optimize performance