Hologres:Optimize query performance

Check whether statistics are correct

You can view the execution plan:

If statistics are not synchronized in time, a suboptimal execution plan may be generated. For example:

The tmp1 table has 10 million rows, and the tmp table has 1,000 rows. In Hologres, the default number of rows in the statistics is 1,000. If you run an explain SQL statement, the result may show that the number of rows for the tmp1 table does not match the actual number of rows. This indicates that the statistics are not updated.

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

Update statistics

analyze tmp;
analyze tmp1;

If the explain result returns rows=1000, it means statistics are missing. Poor performance is often caused by the optimizer lacking statistics. To fix this, update the statistics by running analyze <tablename>. This is a simple and fast way to optimize query performance.

Recommended scenarios for updating statistics

You should run the analyze <tablename> command in the following situations.

• After you import data.

• After you perform many INSERT, UPDATE, or DELETE operations.

• When you need to run ANALYZE for both internal and foreign tables.

• When you need to run ANALYZE on a parent table for a partitioned table.

• If you encounter the following issues, run analyze <tablename> before you run the import task to optimize efficiency.

  • An OOM error occurs during a multi-table join.

    The error message is typically Query executor exceeded total memory limitation xxxxx: yyyy bytes used.

  • The import efficiency is low.

    When you query or import data in Hologres, the efficiency is low and tasks take a long time to complete.

Update statistics

As mentioned previously, if the statistics of tables in a join are not updated in time, the larger table might be used to create a hash table. This reduces join efficiency. You can update the table statistics to improve SQL performance.

analyze <tablename>;

Select an appropriate distribution key

A distribution key is used to partition data across multiple shards. Even partitioning prevents data skew. Designing multiple related tables with the same distribution key can accelerate local joins. When you create a table, select an appropriate distribution key based on the following principles:

• Suggestions for setting a distribution key

  • Select the join condition columns from your join queries as the distribution key.

  • Select columns that are frequently used in `GROUP BY` clauses as the distribution key.

  • Select columns with evenly and discretely distributed data as the distribution key.

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

• Example of setting a distribution key

  For example, when you join the `tmp` and `tmp1` tables, the execution plan from the `explain` SQL statement may show a `Redistribution Motion`. This means data is being redistributed and there is no local join, which leads to low query efficiency. You need to recreate the tables and set the join key as the distribution key to avoid the extra overhead of data redistribution during multi-table joins. The following DDL statements are examples for recreating the two tables.

  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;
  
  -- Set the distribution key to the join key.
  select count(1) from tmp join tmp1 on tmp.a = tmp1.b ;

  After you reset the distribution keys for the tables and run the `explain` SQL statement again, the Motion operator in the red box is removed from the execution plan. The data is distributed among shards using the same hash key. Because the data distribution is the same, the Motion operator is no longer needed. This indicates that data will not be redistributed, which avoids redundant network overhead.

Use Runtime Filter

Starting from V2.0, Hologres supports Runtime Filter. This feature is typically used in multi-table joins, especially in scenarios where a large table is joined with a small table. You do not need to set it manually. The optimizer and execution engine automatically optimize the filtering behavior during the join process at query time. This reduces the amount of data scanned, lowers I/O overhead, and improves join query performance. For more information, see Runtime Filter.

Optimize the Join Order algorithm

• When SQL join relationships are complex or many tables are joined, the query optimizer (QO) spends more time selecting the optimal join order. In certain scenarios, adjusting the join order policy can reduce the time spent on query optimization. You can use the following syntax to set the optimizer's join order algorithm.

  set optimizer_join_order = '<value>'; 

• Parameter description

  Parameter

  Description

  value

  The join order algorithm for the optimizer. The options are as follows. exhaustive2 (default for V2.2 and later): An upgraded and optimized dynamic programming algorithm. exhaustive (default in earlier versions): Uses a dynamic programming algorithm for join order transformation. It generates the optimal execution plan but has the highest optimizer overhead. query: Does not perform join order transformation. It executes joins in the order written in the SQL statement and has the lowest optimizer overhead. greedy: Uses a greedy algorithm to explore the join order. It has a moderate optimizer overhead.

• Additional information

  The default exhaustive2 algorithm performs a global search for the optimal execution plan. However, for joins with many tables, such as more than 10, the optimization time can be high. Using the `query` or `greedy` algorithm can reduce optimizer time, but may not generate the optimal execution plan.

Optimize Motion operators such as Broadcast

Hologres includes four types of Motion Nodes. Each type corresponds to a data redistribution scenario, as shown in the following table.

Based on the results of the `explain` SQL statement, consider the following:

• If a Motion operator is time-consuming, you should redesign the distribution key.

• If incorrect statistics lead to the generation of a Gather Motion or Broadcast Motion, you can use the analyze <tablename> command to change it to the more efficient Redistribute Motion distribution method.

• Broadcast Motion is advantageous only in scenarios with a small number of shards and small broadcast tables. Therefore, for small table broadcast scenarios, you should reduce the number of shards for the table. Try to keep the shard count proportional to the number of workers to improve query efficiency. For more information about shard count, see Shard count.

Use Fixed Plan

Fixed Plan is suitable for high-throughput scenarios. It uses a simplified execution path to achieve significantly higher performance and throughput. For information on how to configure and use it, see Accelerate SQL execution using Fixed Plan.

Rewrite PQE operators

Hologres has multiple underlying execution engines: the native Hologres Query Engine (HQE), which is a vector engine, and the Postgres Query Engine (PQE), which is a distributed Postgres engine. If an SQL statement contains an operator that HQE does not support, the system sends the operator to PQE for execution. In this case, query performance is not fully optimized, and you need to modify the query statement.

When you check the execution plan using an explain SQL query, if the plan contains External SQL (Postgres), it means that part of the SQL statement is executed in PQE.

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

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;

Avoid fuzzy searches

Fuzzy searches, such as the `LIKE` operation, do not use indexes.

Impact of result caching on queries

By default, Hologres caches the results of identical queries or subqueries. Repeated executions of the same query will hit the cache. You can use the following command to disable caching to avoid affecting performance tests:

set hg_experimental_enable_result_cache = off;

Optimization methods for OOM

An OOM error usually occurs when the instance has insufficient computing memory. The following is a common error message. OOM errors can be caused by various factors, such as complex calculations and high concurrency. You can perform targeted optimizations based on the specific cause to reduce OOM errors. For more information, see Troubleshooting guide for OOM issues.

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

Optimization for Order By Limit scenarios

In versions earlier than Hologres V1.3, the Merge Sort operator was not supported for `Order By Limit` scenarios. When an execution plan was generated, an extra sort was performed on the final output, resulting in poor performance. Starting from V1.3, the engine has optimized `Order By Limit` scenarios to support the Merge Sort operator. This enables multi-way merge sort, which eliminates the need for an extra sort and improves query performance.

Count Distinct optimization

• Rewrite as APPROX_COUNT_DISTINCT

  Count Distinct is a precise deduplication method. It requires shuffling records with the same key to the same node for deduplication, which is resource-intensive. Hologres provides the `APPROX_COUNT_DISTINCT` extension function, which uses HyperLogLog cardinality estimation for imprecise `COUNT DISTINCT` calculations to improve query performance. The average error rate can be controlled within 0.1% to 1%. You can rewrite your queries as needed. For more information, see APPROX_COUNT_DISTINCT.

• Use the UNIQ function

  Starting from V1.3, Hologres supports the `UNIQ` function for precise deduplication. When the cardinality of the `GROUP BY` key is high, `UNIQ` performs better and saves more memory than `Count Distinct`. If you encounter an OOM error with `Count Distinct`, you can replace it with `UNIQ`. For more information, see UNIQ.

• Set an appropriate distribution key

  If you have multiple `Count Distinct` operations with the same key and the data is discretely and evenly distributed, you can set the `Count Distinct` key as the distribution key. This ensures that the same data is distributed to the same shard, which avoids data shuffle.

• Count Distinct optimization

  Starting from V2.1, Hologres includes many performance optimizations for `Count Distinct` scenarios. These optimizations cover single `Count Distinct`, multiple `Count Distinct`, data skew, and SQL statements without a `Group By` field. You do not need to manually rewrite the statement to use `UNIQ` to achieve better performance. To improve `Count Distinct` performance, upgrade your Hologres instance to V2.1 or a later version.

Group By optimization

A `Group By` key causes data to be redistributed based on the key of the grouping column during computation. If the `Group By` operation is time-consuming, you can set the `Group By` column as the distribution key.

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

Starting from V4.0, Hologres provides a merge optimization to reduce merges when you group by multiple related columns. For example, a clause such as GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) is rewritten to the equivalent GROUP BY COL_A. Due to the exploration overhead, the search depth is limited to a maximum of five layers. For example:

CREATE TABLE tbl (
    a int,
    b int,
    c int
);

-- Query
SELECT
    a,
    a + 1 as a1,
    a + 2 as a2,
    sum(b)
FROM tbl
GROUP BY
    a,
    a1,
    a2;

The execution plan is shown in the following figure. You can see that a query rewrite was performed, and the GROUP BY clause contains only column A.

You can disable this feature using the following Grand Unified Configuration (GUC) parameters:

-- Disable the feature at the session level.
SET hg_experimental_remove_related_group_by_key = off; 

-- Disable the feature at the database level.
ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

Handle data skew

-- hg_shard_id is a built-in hidden column in each table that describes the shard where the corresponding row of data is located.
select hg_shard_id, count(1) from t1 group by hg_shard_id;

Optimization for With expressions (Beta)

Hologres is compatible with PostgreSQL and supports Common Table Expressions (CTEs). CTEs are commonly used in recursive queries with `WITH`. Because the implementation is based on inlining, as in PostgreSQL, referencing a CTE multiple times causes repeated computation. In Hologres V1.3, you can use the following GUC parameter to enable CTE Reuse. This allows a CTE to be computed once and referenced multiple times, saving computing resources and improving query performance. If your Hologres instance is a version 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;
                                      

• Execution plan comparison

  • Execution plan for a version of Hologres earlier than V1.3 (V1.1)

  • Execution plan for Hologres V1.3

  A comparison of the execution plans shows that versions of Hologres earlier than V1.3 perform multiple AGG computations (HashAggregate). In contrast, Hologres V1.3 computes the result only once and reuses it, which improves performance.

Optimize single-stage Agg to multi-stage Agg

If an Agg operator is time-consuming, check whether local shard-level pre-aggregation is being performed.

set optimizer_force_multistage_agg = on;

Optimization for multiple aggregate functions with the same name

In production scenarios, an SQL statement may contain multiple identical aggregate functions that aggregate the same column. Starting from V4.0, Hologres supports equivalent rewrites to reduce the number of calculations and improve query performance. The following is a usage example:

• Prepare data

  -- Create a test table.
  CREATE TABLE tbl(x int4, y int4);
  
  -- Insert test data.
  INSERT INTO tbl VALUES (1,2), (null,200), (1000,null), (10000,20000);

• Sample query

  SELECT
      sum(x + 1),
      sum(x + 2),
      sum(x - 3),
      sum(x - 4)
  FROM
      tbl;

• The following figure shows the execution plan. You can see that the query is rewritten to optimize the calculations.

  

• You can disable this feature using the following GUC parameters:

  -- Disable the feature at the session level.
  SET hg_experimental_remove_related_group_by_key = off; 
  
  -- Disable the feature at the database level.
  ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

Select a storage class

Hologres supports row-oriented, column-oriented, and row-column hybrid storage modes. You can select a storage class that suits your business scenario, as described in the following table.

Select a data type

Hologres supports multiple data types. You should select a data type that suits your business scenario and requirements based on the following principles:

• Use types that require less storage space.

  • Use the INT type instead of the BIGINT type.

  • Use precise DECIMAL/NUMERIC types. Specify the precision (PRECISION, SCALE) and keep it as small as possible. Reduce the use of non-precise types such as FLOAT or DOUBLE PRECISION to avoid errors in statistical summaries.

  • Do not use non-precise types such as FLOAT or DOUBLE for `GROUP BY` columns.

  • Use TEXT, which is more versatile. When you use VARCHAR(N) and CHAR(N), keep the value of N as small as possible.

  • Use TIMESTAMPTZ or DATE for date types. Avoid using TEXT.

• Use consistent data types for join conditions.

  When you perform multi-table joins, use the same data type for the join columns. This avoids the extra overhead caused by Hologres performing implicit type conversions on columns of different types.

• Avoid using non-precise types such as FLOAT/DOUBLE in operations such as UNION or GROUP BY.

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

Select a primary key

Setting a primary key properly can help the optimizer generate better execution plans in certain scenarios, such as in a query with group by pk,a,b,c.

However, in column store scenarios, setting a primary key can have a significant impact on data write performance. Typically, write performance without a primary key is three times better than with a primary key.

Select a partitioned table

Select an index

call set_table_property('tmp', 'clustering_key', 'a,b,c');
select * from tmp where a > 1 ;  --Can use the cluster index.
select * from tmp where a > 1 and c > 2 ;   --Only 'a' can use the cluster index.
select * from tmp where a > 1 and b > 2 ;  --Both 'a' and 'b' can use the cluster index.
select * from tmp where a > 1 and b > 2 and c > 3 ; --'a', 'b', and 'c' can all use the cluster index.
select * from tmp where b > 1 and c > 2 ;   --Neither 'b' nor 'c' can use the cluster index.

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

You can create the tmp table and specify the index fields using 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 cluster index is used, run the following statement.

  explain select * from tmp where a > 1;

  

• To check whether the bitmap index is used, run the following statement.

  explain select * from tmp where c = 1;

  

• To check whether the segment key is used, run the following statement.

  explain select * from tmp where b > 1;