Hologres 这样还能咋优化？

在Hologres中，当SQL查询涉及大量数据（如IN操作包含大量值）时，生成执行计划的时间可能会较长。这种情况下，可以通过以下优化手段来提升性能：

1. 优化Join Order算法

当SQL中涉及多表关联或复杂条件（如IN操作）时，优化器（QO）在选择最优执行计划时可能会耗费较多时间。可以通过调整optimizer_join_order参数来降低优化器的耗时。

• 默认行为：exhaustive算法会全局探索最优执行计划，但对于复杂SQL（如表数量大于10），优化耗时可能较高。
• 优化建议：
  • 使用query算法：适用于多表关联且表数据量不大的场景，优化器不做额外调整，直接按SQL顺序生成执行计划。

  SET optimizer_join_order = 'query';
  

  • 使用greedy算法：通过贪心算法生成Join Order，优化器开销适中。

  SET optimizer_join_order = 'greedy';
  

注意：不建议将optimizer_join_order设置为DB级别，否则可能影响其他查询的性能。

2. 使用Runtime Filter

从Hologres V2.0版本开始，支持Runtime Filter功能，尤其适用于大表Join小表的场景。Runtime Filter会在查询时自动优化Join过程的过滤行为，减少扫描的数据量，从而降低IO开销和执行计划生成时间。

• 适用场景：IN操作可以视为一种隐式的Join操作，Runtime Filter能够有效减少扫描范围。
• 无需手动配置：优化器和执行引擎会自动应用Runtime Filter。

3. 改写SQL以避免复杂操作

如果IN操作包含大量值，建议将其改写为更高效的查询方式。例如，可以将IN操作替换为JOIN操作，或者将数据存储到临时表中进行关联查询。

• 示例： 原始SQL：

  SELECT * FROM tbl WHERE col IN (val1, val2, ..., valN);
  

  改写为：

  CREATE TEMP TABLE temp_values (val INT);
  INSERT INTO temp_values VALUES (val1), (val2), ..., (valN);
  
  SELECT t.* 
  FROM tbl t
  JOIN temp_values v ON t.col = v.val;
  

优势：通过JOIN操作替代IN操作，优化器可以更好地利用统计信息和索引，减少执行计划生成时间。

4. 更新统计信息

统计信息的准确性对优化器生成高效执行计划至关重要。如果统计信息未及时更新，可能导致优化器生成次优的执行计划，增加执行计划生成时间和查询耗时。

• 更新统计信息：

  ANALYZE table_name;
  

• 检查执行计划：使用EXPLAIN命令查看执行计划，确保优化器正确估计了行数。

5. 启用Fixed Plan

对于高吞吐场景，Fixed Plan可以通过简化的执行路径显著提升性能。如果IN操作涉及大量数据，建议检查是否可以通过Fixed Plan加速执行。

• 检查Query执行引擎类型：

  SELECT * 
  FROM hologres.hg_query_log 
  WHERE query_start >= now() - interval '3 h' 
  AND command_tag IN ('INSERT', 'UPDATE', 'DELETE') 
  AND ARRAY['HQE'] && engine_type 
  ORDER BY query_start DESC 
  LIMIT 500;
  

• 启用Fixed Plan相关GUC参数：

  ALTER DATABASE <databasename> SET hg_experimental_enable_fixed_dispatcher_for_multi_values = on;
  

注意：Fixed Plan对SQL语法有一定限制，需确保SQL符合其支持的特征。

6. 关闭Dictionary Encoding

如果查询涉及大量字符类型字段（如TEXT、VARCHAR），Dictionary Encoding可能会带来额外的Decode开销。可以通过关闭Dictionary Encoding来优化性能。

• 关闭方法：

  BEGIN;
  CREATE TABLE tbl (a INT NOT NULL, b TEXT NOT NULL, c INT NOT NULL, d INT);
  CALL set_table_property('tbl', 'dictionary_encoding_columns', '');
  COMMIT;
  

注意：修改Dictionary Encoding设置会引起数据文件重新编码存储，建议在业务低峰期执行变更。

7. 检查Motion算子耗时

如果执行计划中存在耗时较高的Motion算子（如Broadcast Motion或Gather Motion），可能需要重新设计分布列或调整Shard数量。

• 优化建议：
  • 如果Broadcast Motion耗时较高，建议减少广播表的Shard数量。
  • 如果统计信息错误导致生成Gather Motion，可以通过ANALYZE命令更新统计信息，并尝试将分布方式改为Redistribute Motion。

总结与建议

针对IN操作耗时长的问题，可以从以下几个方面入手优化： 1. 调整optimizer_join_order参数以降低优化器耗时。 2. 利用Runtime Filter减少扫描数据量。 3. 改写SQL，将IN操作替换为JOIN操作。 4. 更新统计信息，确保优化器生成高效执行计划。 5. 启用Fixed Plan以简化执行路径。 6. 关闭不必要的Dictionary Encoding以减少Decode开销。 7. 检查并优化Motion算子的耗时。

通过以上方法，可以有效减少执行计划生成时间并提升查询性能。