Hologres Query Optimizer — Ho记录res 查询 优化器

Prerequisites

This 技能 requires ho记录res-命令行工具 to be 安装ed first:

pip 安装 ho记录res-命令行工具 导出 HO记录RES_技能=ho记录res-查询-优化器

All SQL execution and GUC parameter operations depend on ho记录res-命令行工具 commands (ho记录res sql 运行, ho记录res guc show/设置).

Ho记录res 查询 Execution Plan 分析器

This 技能 helps analyze and 优化 Ho记录res SQL 查询 execution plans using EXPLAIN and EXPLAIN ANALYZE commands.

Version Note: This documentation is based on Ho记录res V1.3.4x+. 升级 your instance for better execution plan readability.

Overview Command Description EXPLAIN Shows estimated execution plan from 查询 优化器 (QO). Reference only. EXPLAIN ANALYZE Shows actual execution plan with real 运行time 指标. Use for optimization. Quick 启动 -- Estimated plan (no execution) EXPLAIN SELECT FROM my_table WHERE id > 100;

-- Actual plan with 运行time 指标 (执行s 查询) EXPLAIN ANALYZE SELECT FROM my_table WHERE id > 100;

Reading EXPLAIN 输出

Read execution plans 机器人tom-up. Each arrow (->) represents a node/operator.

Parameter Description cost Estimated cost: 启动up_cost..total_cost. Parent includes child costs. rows Estimated 输出 rows. rows=1000 indicates missing statistics — 运行 ANALYZE . width Estimated average 输出 width (bytes). Reading EXPLAIN ANALYZE 输出

EXPLAIN ANALYZE includes four sections: 查询 Plan, ADVICE, Cost, and Resource.

查询 Plan 指标

格式化: [dop_in:dop_out id=X dop=N time=max/avg/min rows=total(max/avg/min) mem=max/avg/min open=X 获取_next=Y]

Metric Description dop_in:dop_out Parallelism ratio (e.g., 21:1 for gather, 21:21 for shuffle) dop Actual parallelism degree (matches shard count) time Total time = open + 获取_next (ms). Cumulative from children. rows 输出 rows: total(max/avg/min). Large variance = data skew. mem Memory: max/avg/min open Initialization time. 哈希 operators build tables here. 获取_next Data fetch time. Called repeatedly until complete.

导入ant: time is cumulative. Current operator time = current time - child time.

ADVICE Section

系统-生成d suggestions:

Missing 索引es: Table xxx misses bitmap 索引 Missing statistics: Table xxx Miss Stats! please 运行 'analyze xxx'; Data skew: shuffle data skew! max rows is X, min rows is Y Cost Breakdown Metric Description Total cost 查询 total time (ms) 优化器 cost QO plan generation time 启动 查询 cost Pre-execution init (模式 同步, locking) 获取 the first block cost Time to first record batch 获取 结果 cost Time to all 结果s Resource Consumption

格式化: total(max_worker/avg_worker/min_worker)

Metric Description Memory Total and per-worker memory CPU time Cumulative CPU time across cores Physical read bytes Disk reads (缓存 miss) Read bytes Total reads (disk + 缓存) Common Operators

For detAIled operator reference, see references/operators.md.

扫描 Operators Operator Description Seq 扫描 Full table 扫描 索引 扫描 using Clustering_索引 Column-store 索引 扫描 索引 Seek (pk_索引) Row-store primary key 扫描 过滤器 Operators Operator Description 过滤器 No 索引 hit — 添加 索引es Segment 过滤器 Segment key hit Cluster 过滤器 Clustering key hit Bitmap 过滤器 Bitmap 索引 hit Data Movement Operator Description Local Gather Merge files within shard Gather Merge shards to final 结果 Redistribution Data shuffle — 检查 distribution_key Broadcast Small table broadcast to all shards Join Operators Operator Description 哈希 Join 哈希-based join (ensure small table is 哈希 table) Nested Loop Nested loop join (avoid for large data) Cross Join 优化d non-equi join (V3.0+) Aggregation Operator Description 哈希聚合 哈希-based aggregation Partial/Final 哈希聚合 Multi-stage aggregation Other Operator Description 排序 ORDER BY Limit Row limit (检查 if pushed to 扫描) 执行ExternalSQL PQE execution — rewrite for HQE Optimization 工作流 运行 EXPLAIN ANALYZE on slow 查询 检查 ADVICE section for immediate fixes Identify 机器人tleneck operators (highest time) 应用ly tar获取ed optimizations: Issue Symptom Solution Missing stats rows=1000 ANALYZE

-- Join order control (for complex multi-table joins) 设置 优化器_join_order = '查询'; -- Follow SQL order 设置 优化器_join_order = 'greedy'; -- Greedy algorithm

-- Disable Cross Join 设置 hg_experimental_enable_cross_join_rewrite = off;

To persist these 设置tings at database level, use the 命令行工具:

ho记录res guc 设置 优化器_force_multistage_agg on ho记录res guc 设置 优化器_join_order 查询

Best Practices Always use EXPLAIN ANALYZE for production analysis 运行 ANALYZE after 签名ificant data changes De签名 distribution_key based on JOIN/GROUP BY pa

Data shuffle Redistribution Fix distribution_key Wrong 哈希 table Large table as 哈希 更新 statistics No 索引 过滤器 only 添加 clustering/bitmap 索引 PQE execution 执行ExternalSQL Rewrite to HQE functions Data skew Large max/min variance Review distribution Key GUC Parameters -- Multi-stage aggregation 设置 优化器_force_multistage_agg = on;