Hologres:Optimize the performance of queries on Hologres internal tables

Check whether statistics are correct

• By viewing execution plans

  If the statistics are outdated, a poor execution plan may be generated.

  In this example, the tmp1 table contains 10 million rows of data. The tmp table contains 1,000 rows of data. In Hologres, the default value of the rows parameter is 1000. The following execution plan returned by the EXPLAIN statement indicates that the number of rows in the tmp1 table is 1,000, which is incorrect. This indicates that the statistics are not updated.

  Seq Scan on tmp1 (cost=0.00..5.01 rows=1000 width=1)

  

• By viewing system tables

  You can check whether the statistics such as the number of rows and the column length are correct by viewing system tables.

  • Query the settings of the analyze_tuple column in the system table named hologres.hg_table_properties to check whether the number of rows is correct. You can also view the value of the rows parameter in the Scan node.

  • Query the system table named hologres.hg_stats and obtain its information, such as the histogram of each column, average column length, and number of distinct values, as shown in the following figure.

Update statistics

analyze tmp;
analyze tmp1;

If rows=1000 is displayed for the tmp1 table in the execution result of the EXPLAIN statement, the statistics are not synchronized to the query optimizer. Low query performance is usually caused by a lack of statistics. You can execute the ANALYZE statement to collect statistics and synchronize the statistics to the query optimizer in time.

Scenarios for updating statistics

We recommend that you execute the ANALYZE statement in the following situations:

• Data is imported.

• A large number of INSERT, UPDATE, and DELETE operations are performed.

• Statistics need to be collected for both internal and foreign tables.

• Statistics need to be collected for a parent table.

• If you encounter one of the following issues, you must execute the ANALYZE statement before you run an import task. This can improve efficiency.

  • An OOM exception occurs when multiple tables are joined.

    In most cases, the "Query executor exceeded total memory limitation xxxxx: yyyy bytes used" error message is reported.

  • Import efficiency is low.

    When you query or import data in Hologres, the efficiency of the system decreases and tasks take longer than expected to complete.

Update statistics

If you do not update the statistics about tables that are involved in the JOIN operation in a timely manner, a hash operation may be performed on a table with a large amount of data. As a result, the JOIN efficiency becomes low. In this case, you can update the statistics about the tables to improve SQL performance.

analyze <tablename>;

Select an appropriate distribution key

A distribution key is used to divide data into multiple shards evenly. This can help prevent data skew. If you specify the join keys of multiple tables to constitute a distribution key and perform a local JOIN operation on the tables based on the distribution key, queries can be accelerated. When you create a table, you can select an appropriate distribution key based on the following rules:

• Suggestions on distribution key settings

  • Specify join keys to constitute a distribution key.

  • Select columns that are frequently used in the GROUP BY clause to constitute a distribution key.

  • Select columns in which data is evenly distributed and discrete to constitute a distribution key.

  • For more information about the principles and usage of a distribution key, see Distribution key.

• Sample scenario of setting an appropriate distribution key

  In this example, the execution result of the EXPLAIN statement indicates that the execution plan for querying the tmp and tmp1 tables contains a redistribution operation but no local JOIN operation. As a result, query efficiency is low. You must re-create the two tables and specify the join keys to constitute the distribution key. This prevents additional overheads caused by data redistribution when multiple tables are joined. You can execute the following DDL statements to re-create the two tables and specify the join keys to constitute the distribution key:

  begin;
  create table tmp(a int, b int, c int);
  call set_table_property('tmp', 'distribution_key', 'a');
  commit;
  
  begin;
  create table tmp1(a int, b int, c int);
  call set_table_property('tmp1', 'distribution_key', 'b');
  commit;
  
  -- Specify the join keys to constitute the distribution key. 
  select count(1) from tmp join tmp1 on tmp.a = tmp1.b ;

  After you reset the distribution key for the two tables, execute the EXPLAIN statement again. The execution result of the statement indicates that data is evenly distributed to shards by hash key. In addition, the Redistribute Motion operator, as framed in red in the preceding figure, is optimized. This indicates that data is not redistributed and additional network overheads are prevented.

Use the Runtime Filter feature

Hologres V2.0 and later support the Runtime Filter feature. This feature is suitable for scenarios where two or more tables are joined for queries, especially for scenarios where large tables are joined with small tables. After the Runtime Filter feature is enabled, the optimizer and execution engine automatically optimize the filter operation during queries, without the need for manual configurations. After the optimization, the I/O overhead is reduced and the performance of join queries is improved because a smaller amount of data is scanned. For more information, see Runtime Filter.

Select a join order algorithm for the query optimizer

• If complex SQL join operations are involved or many tables are joined, the query optimizer takes an extended period of time to select the optimal connection. If the join order policy is adjusted, the amount of time required to optimize queries may be reduced. The following syntax is used to select a join order algorithm for the query optimizer:

  set optimizer_join_order = '<value>'; 

• Parameter description

  Parameter

  Description

  value

  The join order algorithm to be used by the query optimizer. Valid values: exhaustive (default value): uses a dynamic planning algorithm to obtain the optimal join order. This generates the optimal execution plan. However, this algorithm has the highest query optimizer overheads. query: uses the join order that is specified in the SQL statement. This algorithm has the lowest query optimizer overheads. greedy: uses a greedy algorithm to obtain a better join order. This algorithm has moderate query optimizer overheads.

• Usage notes

  You can use the exhaustive algorithm to obtain the globally optimal join order. The amount of time required for optimization depends on the number of tables that you want to join. For example, if more than 10 tables are to be joined, more time may be required to obtain the optimal join order. If you use the query or greedy algorithm to obtain a join order, the required time can be reduced, but the generated execution plan is not optimal.

Optimize Motion operators

The following table describes the four types of Motion operators in Hologres.

When you view the execution result of the EXPLAIN statement, take note of the following items:

• If the Motion operators are time-consuming, you can redesign the distribution key.

• If a Gather Motion or Broadcast Motion operator is generated due to invalid statistics, you can execute the ANALYZE <tablename> statement to help generate the more efficient Redistribute Motion operator.

• You can execute the following statements to prohibit the generation of Motion operators and then compare the query time of different Motion operators:

  -- Prohibit the generation of the Broadcast Motion operator. 
  set optimizer_enable_motion_broadcast = off; 
  
  -- Prohibit the generation of the Redistribute Motion operator. 
  set optimizer_enable_motion_redistribute = off; 

• The Broadcast Motion operator is superior only in scenarios in which both the number of shards and the number of broadcast tables are small. If the Broadcast Motion operator is to be performed on a small table, we recommend that you reduce the number of shards in the table to improve query efficiency. Try to keep the shard count proportional to the number of workers. For more information about shard counts, see User guide of table groups and shard counts.

Optimize data writes and updates by using fixed plans

Fixed plans of Hologres are specific for high-throughput data write, update, and delete scenarios. Simplified execution paths help improve performance and throughput by multiple times. For more information about fixed plans, see Accelerate the execution of SQL statements by using fixed plans.

Change PostgreSQL Query Engine (PQE) operators

Hologres supports multiple execution engines at the underlying layer, such as Hologres Query Engine (HQE) and PQE. If an SQL statement contains an operator that is not supported by HQE, Hologres sends the operator to PQE for execution. In this case, query performance is not optimal. You must optimize the SQL statement.

You can execute the EXPLAIN statement to check the execution plan of an SQL statement. If the execution plan contains External SQL(Postgres), the SQL statement is executed in PQE.

explain select * from tmp where a not in (select a from tmp1);

After optimization, execute the following statement to check the execution plan of the SQL statement. The execution plan indicates that the SQL statement is not executed in PQE.

explain select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select name,unnest(string_to_array(age,',')) from demo;

select cast(substring(c1, 13, 2) as int) AS hour from t2;

select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;

select regexp_replace(c1::text,'-','0') from t2;

select replace(c1::text,'-','') from t2;

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')  at time zone 'utc') from t2

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;

select cast(c1 as timestamp) from t2;

select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;

select c1::text from t2;

select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;

Do not perform fuzzy searches

No index is created in a fuzzy search operation such as LIKE.

Disable caching of query results

By default, Hologres caches query or subquery results. Cached results are returned for repeated queries. You can execute the following statement to disable the caching of query results to prevent the impact on query performance:

set hg_experimental_enable_result_cache = off;

Reduce OOM exceptions

If the memory used for computing on an instance is insufficient, an OOM exception may occur. The following example shows a common OOM exception. OOM exceptions can be caused by multiple reasons, such as complex computing and high concurrency. You can perform optimization accordingly to reduce OOM exceptions. For more information, see FAQ about OOM.

Total memory used by all existing queries exceeded memory limitation. 
memory usage for existing queries=(2031xxxx,184yy)(2021yyyy,85yy)(1021121xxxx,6yy)(2021xxx,18yy)(202xxxx,14yy); Used/Limit: xy1/xy2 quota/sum_quota: zz/100

Optimize the ORDER BY LIMIT operation

In versions earlier than Hologres V1.3, the Merge Sort operator is not supported in the ORDER BY LIMIT operation. In the execution plan of a statement that contains the ORDER BY LIMIT operation, the final results are sorted before the results are exported. This results in relatively low query performance. In Hologres V1.3 and later, the ORDER BY LIMIT operation is optimized to support the Merge Sort operator. The optimization merges and sorts multiple data streams without additional sorting. This improves query performance.

Optimize the COUNT DISTINCT function

• Change COUNT DISTINCT to APPROX_COUNT_DISTINCT.

  The COUNT DISTINCT function removes duplicate data precisely. The records with the same key must be distributed to the same node for deduplication by using the function. This is resource-consuming. Hologres supports the extended function APPROX_COUNT_DISTINCT. The HyperLogLog algorithm is used to approximate the number of distinct values to perform imprecise COUNT DISTINCT computing. This improves query performance. The average error rate can be controlled between 0.1% and 1%. You can change COUNT DISTINCT to APPROX_COUNT_DISTINCT based on your business requirements. For more information, see APPROX_COUNT_DISTINCT.

• Use the UNIQ function

  In Hologres V1.3 and later, you can use the UNIQ function to precisely remove duplicate data. If the cardinality of the GROUP BY key is high, the UNIQ function performs better and saves more memory than the COUNT DISTINCT function. If an OOM exception occurs when you use the COUNT DISTINCT function, you can use the UNIQ function instead. For more information, see UNIQ.

• Select an appropriate distribution key

  If multiple COUNT DISTINCT functions with the same key exist, and data is evenly distributed and discrete, we recommend that you specify the key of the COUNT DISTINCT functions as the distribution key. This way, the same data can be distributed to the same shard. This prevents data shuffling.

• Optimize the COUNT DISTINCT function

  In Hologres V2.1 and later, Hologres optimizes the performance for COUNT DISTINCT scenarios, including a single COUNT DISTINCT function, multiple COUNT DISTINCT functions, data skew, and SQL statements that do not contain the GROUP BY clause. To improve query performance, you do not need to manually change the COUNT DISTINCT function to the UNIQ function. If you want to improve the performance of the COUNT DISTINCT function, we recommend that you upgrade the version of your Hologres instance to V2.1 or later.

Optimize the GROUP BY operation

The GROUP BY Key operation allows data to be redistributed based on the key of the column in the GROUP BY clause. If the GROUP BY operation is time-consuming, you can use the column in the GROUP BY clause as the distribution key.

-- If data is distributed based on the values in Column a, data redistribution during runtime is reduced. The parallel computing capability of shards is fully utilized. 
select a, count(1) from t1 group by a; 

Resolve the issue of data skew

-- The hg_shard_id column is the built-in hidden column of each table. This column provides the ID of the shard where each row of data is located.
select hg_shard_id, count(1) from t1 group by hg_shard_id;

Optimize WITH queries (beta)

Hologres is compatible with PostgreSQL and supports common table expressions (CTEs). CTEs are commonly used in recursive queries in a WITH clause. The implementation is the same as that in PostgreSQL, in which CTEs are inlined. Therefore, repeated computing occurs when a CTE is used multiple times. In Hologres V1.3, you can set the GUC parameter optimizer_cte_inlining to off to enable the CTE reuse feature. This way, a CTE can be computed only once and referenced multiple times to save computing resources and improve query performance. If the version of your Hologres instance is earlier than V1.3, upgrade your instance.

set optimizer_cte_inlining=off;

• Example

  create table cte_reuse_test_t
  (
      a integer not null,
      b text,
      primary key (a)
  );
  
  insert into cte_reuse_test_t values(1, 'a'),(2, 'b'), (3, 'c'), (4, 'b'), (5, 'c'), (6, ''), (7, null);
  
  
  set optimizer_cte_inlining=off;
  
  explain with c as (select b, max(a) as a from cte_reuse_test_t group by b)
  select a1.a,a2.a,a1.b, a2.b
  from c a1, c a2
  where a1.b = a2.b
  order by a1.b
  limit 100;
                                      

• Comparison of execution plans

  • The following figure shows the execution plan in Hologres V1.1. The execution plan in other Hologres versions earlier than V1.3 is similar to that in Hologres V1.1.

  • The following figure shows the execution plan in Hologres V1.3.

  The comparison of the execution plans indicates that the execution plan in a Hologres version earlier than V1.3 contains multiple HashAggregate operations. In the execution plan in Hologres V1.3, the results are computed only once and then reused. This improves query performance.

Optimize single-stage aggregation to multi-stage aggregation.

If aggregate operators are time-consuming, check whether local shard-level pre-aggregation is performed.

set optimizer_force_multistage_agg = on;

Select a storage mode

Hologres supports row-oriented, column-oriented, and row-column hybrid storage modes. You can select a storage mode that best suits your business scenario. The following table describes the storage modes.

Select a data type

Hologres supports a variety of data types. You can select a data type that best meets your business requirements. The following rules apply:

• Select a type that requires minimal storage space.

  • Preferentially use the INT type rather than the BIGINT type.

  • Preferentially use the DECIMAL or NUMERIC type with minimal PRECISION or SCALE. To prevent statistical errors, do not use the FLOAT or DOUBLE PRECISION type.

  • Do not use the FLOAT or DOUBLE type for columns in the GROUP BY clause.

  • Preferentially use the TEXT type rather than the VARCHAR(N) and CHAR(N) types. Set the value of N as small as possible.

  • Use the TIMESTAMPTZ and DATE types rather than the TEXT type to represent a date.

• Use consistent data types for JOIN operations.

  When you join tables, make sure that the columns that match each other use the same data type. This prevents additional overheads caused if Hologres implicitly converts the data type of columns to different data types.

• Do not use the FLOAT or DOUBLE type in operations such as UNION and GROUP BY.

  Operations such as UNION and GROUP BY do not support the DOUBLE PRECISION or FLOAT type. You must use the DECIMAL type.

Select a primary key

Appropriate settings of primary keys can help the query optimizer generate appropriate execution plans in specific scenarios. For example, GROUP BY pk,a,b,c is executed based on an appropriate primary key to accelerate data queries.

In column-oriented storage mode, primary keys may degrade the performance of data writing. In most cases, the performance of data writing if no primary keys are configured is two times higher than that if primary keys are configured.

Select partition table

Select an index

call set_table_property('tmp', 'clustering_key', 'a,b,c');
select * from tmp where a > 1 ;  -- The clustering key can be used. 
select * from tmp where a > 1 and c > 2 ;   -- The clustering key can be used only for Column a. 
select * from tmp where a > 1 and b > 2 ;  -- The clustering key can be used for both Column a and Column b. 
select * from tmp where a > 1 and b > 2 and c > 3 ; -- The clustering key can be used for Column a, Column b, and Column c. 
select * from tmp where b > 1 and c > 2 ;   -- The clustering key cannot be used for Column b or Column c.

call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
select * from tmp where a = 1 and b = 2 ;  --The bitmap index can be used. 
select * from tmp where a = 1 or b = 2 ;  --The bitmap index can be used.

Check whether an index is used

Create a table named tmp and specify index columns by executing the following statements:

begin;
create table tmp(a int not null, b int not null, c int not null);
call set_table_property('tmp', 'clustering_key', 'a');
call set_table_property('tmp', 'segment_key', 'b');
call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
commit;

• To check whether the clustering key is used, execute the following statement:

  explain select * from tmp where a > 1;

  

• To check whether the bitmap index is used, execute the following statement:

  explain select * from tmp where c = 1;

  

• To check whether the segment key is used, execute the following statement:

  explain select * from tmp where b > 1;