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.

  GUC 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 the INSERT, UPDATE, and DELETE statements and prefix query statements.	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 whether to allow the 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 the 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 used. An UPDATE statement that meets conditions is automatically processed by using a fixed plan. If you want to update data in multiple rows by using the UPDATE statement, you must configure set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.
  hg_experimental_enable_fixed_dispatcher_for_delete	Specifies whether to allow the execution of the 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 used. A DELETE statement that meets conditions is automatically processed by using a fixed plan. If you want to delete data from multiple rows by using the DELETE statement, you must configure set hg_experimental_enable_fixed_dispatcher_for_multi_values =on.
  hg_experimental_enable_fixed_dispatcher_for_scan	Specifies whether to allow the execution of a prefix query statement by using a fixed plan. Note In a prefix query, only the first several primary key columns of a table are specified in the query statement. Hologres does not support the execution of a prefix query on a column-oriented table by using a fixed plan.	off	We recommend that you use this parameter for 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 node 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.
  hg_experimental_disable_fixed_planner_conflict_pk_check	Specifies whether to allow non-primary key columns to be specified in the INSERT INTO <table_name> VALUES (...) ON CONFLICT(<column>) syntax. false: not allowed. true: allowed. Note If this parameter is set to true, non-primary key columns are allowed. In actual situations, the INSERT ON CONFLICT statement is executed based on ON CONFLICT(pk).	false	In Hologres V1.3 to V2.1.28, columns specified in ON CONFLICT(<column>) must be primary key columns. In Hologres V2.1.29 and later, you can use the GUC parameter to specify whether to allow non-primary key columns in ON CONFLICT(<column>).

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

    The following example is for your reference:

    -- Check whether the fixed plan feature is enabled at the instance level.
    SHOW hg_experimental_enable_fixed_dispatcher;

  • Configure a GUC parameter

    • Configure a GUC parameter at the session level

      You can use the SET statement to configure a GUC parameter at the session level. If you configure a 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 execute the following statement before you execute the SQL statement.

      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 configure for the GUC parameter.

      The following example is for your reference:

      -- Allow the execution of the INSERT ON CONFLICT statement that writes data to multiple rows by using a fixed plan.
      SET hg_experimental_enable_fixed_dispatcher_for_multi_values =on;

    • Configure a GUC parameter at the database level

      You can execute the alter database xx set xxx statement to configure the GUC parameter at the database level. After you configure the GUC parameter, you must reconnect the connection to make the configuration take effect. For a new database, you must manually reconfigure the setting. Sample statement:

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

      Replace db_name with the name of the database, GUC_name with the name of the GUC parameter, and values with the value that you want to configure for the GUC parameter.

      The following example is for your reference:

      -- Enable the fixed plan feature for a 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. The columns to be queried and those specified in the WHERE clause of a SELECT statement must be of the following data types:

  • 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 statement

• Syntax

  To use fixed plans, execute the INSERT statement 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 the INSERT statement to write data to Hologres internal tables but not foreign tables.

  • You can execute the INSERT statement to write data to Hologres partitioned tables. In Hologres V1.3 and later, you can execute the INSERT statement to write data to a parent partitioned table.

• Execute the INSERT ON CONFLICT statement to write data to a single row

  • The following types of INSERT statements are supported:

    • An INSERT statement that does not contain 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 some of the non-primary key columns. However, you must also specify columns to be updated in the col = excluded.col format.

  • The following example is for your reference:

    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 primary key columns and 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, 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. 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 the INSERT ON CONFLICT statement 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 GUC parameter hg_experimental_enable_fixed_dispatcher_for_multi_values 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 some 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 selectunnest (ARRAY[TRUE, FALSE, TRUE]::bool[]), unnest(ARRAY[1, 2, 3]::int4[]), unnest(ARRAY[1.11, 2.222, 3]::float4[])
    ON CONFLICT xxx;
    

    • The GUC parameter hg_experimental_enable_fixed_dispatcher_for_multi_values 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.

    The following example is for your reference:

    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 to be updated are specified in the col = excluded.col format.

• Execute the UPDATE or INSERT statement with conditional clauses

  To cope with the out-of-order requirements of the input data in the rows that share the same primary key and to support HBase CheckAndPut, Hologres allows you to use fixed plans to execute the INSERT or UPDATE statement with conditional clauses.

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

  • 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.

  The following example is for your reference:

  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 (pk)
      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 (pk)
      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 (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;
  
  -- 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 (pk)
      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 (pk)
      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.

  • 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 GUC parameter hg_experimental_enable_fixed_dispatcher_for_multi_values must be set to on.

  • In Hologres V1.3 and later, you can use fixed plans to execute the INSERT ON CONFLICT statement 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 the INSERT ON CONFLICT statement on a table that contains a column with the DEFAULT constraint.

  The following example is for your reference:

  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.

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

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

  The following example is for your reference:

  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 statement

• Syntax

  To use fixed plans, execute the UPDATE statement in the following syntax:

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

• Usage notes

  When you execute the UPDATE statement 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 partitioned tables instead of a parent partitioned table. The specified table must have a primary key.

  • The GUC parameter hg_experimental_enable_fixed_dispatcher_for_update must be set to on. In Hologres V1.3.25 and later, this parameter is no longer used. An UPDATE statement that meets conditions is automatically processed by using a fixed plan. If you want to update data in multiple rows by using the UPDATE statement, you must set the GUC parameter hg_experimental_enable_fixed_dispatcher_for_multi_values 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 use 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.

  The following example is for your reference:

  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 statement

• Syntax

  To use fixed plans, execute the DELETE statement in the following syntax:

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

• Usage notes

  When you execute the DELETE statement 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 partitioned tables instead of a parent partitioned table. The specified table must have a primary key.

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

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

  • You can use 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.

  The following example is for your reference:

  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 the UPDATE statement for more examples.
  SET hg_experimental_enable_fixed_dispatcher_for_delete = ON;
  
  DELETE FROM test_delete
  WHERE pk1 = 1
      AND pk2 = 2;
  

SELECT statement

• Syntax

  To use fixed plans, execute the SELECT statement 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 partitioned tables instead of a parent partitioned table.

  • The specified table must have a primary key.

• Perform point queries of key-value pairs

  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 use 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 returned result, the value of the limit must be greater than 0.

  The following example is for your reference:

  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;
  
  -- In this example, a fixed plan can be used.
  SELECT * FROM test_select WHERE pk1 = 1 AND pk2 = 2;

• Perform 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 < ?;-- 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.                                

  • Usage notes

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

    • The GUC parameter hg_experimental_enable_fixed_dispatcher_for_scan=on; must be set to on. The version of your Hologres instance must be V1.3.35 or later.

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

    • You must specify only prefixes 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 (pk1,pk2,pk3) is specified for the primary key, (pk1) or (pk1,pk2) is used as the prefix.

    • Prefix queries can be performed on only row-oriented tables, including row-column hybrid 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 returned result, the value of the limit must be greater than 0.

    Note

    Prefix queries return all result rows at a time. If the size of the rows exceeds the limit specified by the hg_experimental_fixed_scan_bytesize_limit parameter, the error message scan result size larger than fixed scan size limit is reported. You can set the hg_experimental_fixed_scan_bytesize_limit parameter to an appropriate value. The default value is 1048576, which indicates 1 MB.

    If (pk1,pk2,pk3,pk4) is configured for the primary key, pk1,pk3 is configured for the distribution key.

    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;
    
    -- 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 use the pk in (?,?,?) or pk = ANY() format to query multiple rows at a time. Syntax:

    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.

  • The following example is for your reference:

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

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 the INSERT, UPDATE, and DELETE statements by using fixed plans to improve data update efficiency.

• You can execute the EXPLAIN statement to view the SQL execution plan and verify that a fixed plan is used. 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.

Optimize performance

FAQ

• Question 1: What do I do if the error message role/database does not exist. is reported for a connection?

  • Cause: The user account or database does not exist.

  • Solution: Check the connection information and make sure that the username or database name is valid.

    You can log on to the Hologres console, find the desired instance, and click Manage in the Actions column. Click Account Management in the left-side navigation pane of the Instance Details page. Then, you can obtain the username on the User Management page and the database name on the Database Authorization page.

• Question 2: What do I do if the error message the requested table name: xxx (id: xx, version: xx) mismatches the version of the table (id: xx, version: xx) from server is reported during data writes?

  • Cause: The table metadata changes due to operations such as column addition during data writes. As a result, the table version changes.

  • Solution: Re-establish the connection. In fixed plans, the new table metadata is obtained and the data is written.