hologres-slow-query-analysis

⚠️ Pre-execution readiness check (completed before all analysis steps)：

1. Already pip install hologres-cli, and hologres status can connect to the target instance (which instance / database to connect to is determined by the current profile, and can be switched with --profile <name> ).
2. export HOLOGRES_SKILL=hologres-slow-query-analysis, to facilitate post-hoc auditing.

RAM permissions, slow log read authorization (pg_read_all_stats / spm_grant), version matrix, engine type semantics see references/preconditions.md。

Hologres slow SQL analysis

Based on the hologres.hg_query_log view, analyze slow SQL of the Hologres instance and outputstructured diagnostic conclusions + optimization recommendations, answering: which SQL is most worth optimizing first, why it is slow, and how it should be optimized. All SQL is executed via hologres sql run --no-limit-check executed.

Input parameters

Parameter	Required	Description
start_time	Yes	Analysis start time, ISO-8601 (Beijing time must include +08:00）
end_time	Yes	Analysis end time, same format as start_time
query_id	No	Single SQL identifier; when provided, prioritize single-SQL analysis

The database is determined by the current profile's database decides, and there is no need and no way to specify it on the command --db-name; to switch databases use hologres config set database <db> or --profile <name>。

Decision logic

Input conditions	Execution flow	See details in
Not provided query_id	Overall instance slow SQL analysis — 6 steps: overall summary → multi-dimensional profiling → priority optimization targets → in-depth analysis of key SQL → error SQL classification → final conclusion	references/instance-flow.md
Provided query_id	Single SQL analysis — 5 steps: locate record → determine slowness dimension → in-depth plan/statistics analysis → cause summary → optimization recommendations	references/single-query-flow.md

The specific SQL, output templates, and judgment thresholds for each step are in the corresponding references files.Each diagnostic item must output a summary in a fixed format, template see output-spec.md。

Overall instance slow SQL analysis (intent overview)

Step	Purpose	Main query / rule
1	Retrieve total / successful / failed SQL counts within the time period	instance-flow §1
2	Perform overall profiling across multiple dimensions such as digest / user / application / SQL type / read-write tables / resources, etc.	instance-flow §2(Independent SQL should beparallelexecuted)
3	Select the SQL that should be optimized first (P0 / P1 / P2)	Priority determination see diagnosis-rules.md
4	Perform in-depth plan / statistics / resource characteristic analysis on key SQL	instance-flow §4
5	Classify and summarize error SQL by SQLSTATE	instance-flow §5 + error-codes.md
6	Output final conclusion + optimization list (including query_id and the complete original SQL text, which must not be truncated)	instance-flow §6 + output-spec.md

Single SQL analysis (intent overview)

Step	Purpose	Key content
1	Via query_id retrieve the complete record	single-query-flow §1
2	Determine where the slowness is (scan / filter / memory / CPU / engine)	duration / read_bytes / memory_bytes / cpu_time_ms / engine_type
3	Combine plan / statistics / agg_stats / query_detail / extended_info to dig into the cause of slowness	Engine sub-checklist see single-query-flow §3
4	Output primary / secondary bottlenecks and the primary modification points	Slowness cause determination rules see diagnosis-rules.md
5	Provide actionable optimization suggestions (core / high-benefit / auxiliary)	single-query-flow §5

General rules for output templates

Each diagnostic item must be output in the following fixed format (specific fields are refined within each step template):

【诊断项名称】
- 指标口径：{统计口径说明}
- 关键结果：{核心数值或 Top 对象（含 query_id）}
- 典型完整 SQL：query_id={...}，SQL={完整 query 原文，不可截断}
- 现象总结：{现象描述}
- 原因判断：{原因分析}
- 建议：{优化建议}
- 结论：{一句话总结}

For the complete output specification (title / analyst / engine type rules / instance-level vs single-SQL output checklist), see references/output-spec.md。

References

Topic	File
Prerequisites / version / permissions / hg_query_log fields / engine type semantics	preconditions.md
6-step process for overall slow SQL analysis of an instance (including SQL + output template)	instance-flow.md
5-step process for single-SQL analysis (including SQL + output template)	single-query-flow.md
Prioritized optimization rules + slow-cause determination rules	diagnosis-rules.md
Output specification (title / analyst / engine rules / output checklist)	output-spec.md
Complete SQLSTATE error code mapping (PostgreSQL + Hologres)	error-codes.md
Supplementary diagnostic SQL library (new-query / traffic / peak period, etc.)	diagnostic-queries.md
Slow log export (internal table / OSS)	log-export.md
Log configuration parameters (log_min_duration_statement , etc.)	configuration.md

Notes

1. hologres.hg_query_log By default, approximately one month is retained, with a maximum of 10,000 records per query; queries must include query_start range conditions, do not use to_char(query_start, ...) wrapping (cannot use indexes).
2. engine_type is an array type; when aggregating, use engine_type::text; FixedQE-type SQL is usuallynot the bottleneckin slow SQL diagnostics; it is recommended to filter engine_type::text NOT LIKE '%FixedQE%'in the digest aggregation SQL (see instance-flow §2.1）。
3. digest The field has been supported since V2.2; it is empty for lower-version instances and must be downgraded to aggregation by query text.
4. When outputting erroneous SQL / prioritized optimization targets / key SQL,query the original textmust not be truncated, and must be accompanied by query_id。
5. When hologres sql run returns an empty result (no data), it is forbidden to modify parameters on your own to retry or work around to obtain data; directly skip that step and mark "no data" in the output result.