hologres-query-optimizer

name

hologres-query-optimizer

description

Hologres Query Execution Plan Analyzer and Optimizer. Use for analyzing SQL performance issues, understanding EXPLAIN/EXPLAIN ANALYZE output, interpreting query operators, and providing optimization recommendations for Hologres queries. Triggers: "hologres explain", "query plan", "execution plan", "sql optimization", "query performance", "hologres performance", "slow query", "query optimizer", "explain analyze"

Prerequisites

This skill requires hologres-cli to be installed first:

pip install hologres-cli
export HOLOGRES_SKILL=hologres-query-optimizer

All SQL execution and GUC parameter operations depend on hologres-cli commands (hologres sql run, hologres guc show/set).

Hologres Query Execution Plan Analyzer

This skill helps analyze and optimize Hologres SQL query execution plans using EXPLAIN and EXPLAIN ANALYZE commands.

Version Note: This documentation is based on Hologres V1.3.4x+. Upgrade your instance for better execution plan readability.

Overview

Command	Description
EXPLAIN <sql>	Shows estimated execution plan from Query Optimizer (QO). Reference only.
EXPLAIN ANALYZE <sql>	Shows actual execution plan with real runtime metrics. Use for optimization.

Quick Start

-- Estimated plan (no execution)
EXPLAIN SELECT * FROM my_table WHERE id > 100;

-- Actual plan with runtime metrics (executes query)
EXPLAIN ANALYZE SELECT * FROM my_table WHERE id > 100;

Reading EXPLAIN Output

Read execution plans bottom-up. Each arrow (->) represents a node/operator.

Parameter	Description
cost	Estimated cost: startup_cost..total_cost. Parent includes child costs.
rows	Estimated output rows. rows=1000 indicates missing statistics — run ANALYZE <table>.
width	Estimated average output width (bytes).

Reading EXPLAIN ANALYZE Output

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

Query Plan Metrics

Format: [dop_in:dop_out id=X dop=N time=max/avg/min rows=total(max/avg/min) mem=max/avg/min open=X get_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 + get_next (ms). Cumulative from children.
rows	Output rows: total(max/avg/min). Large variance = data skew.
mem	Memory: max/avg/min
open	Initialization time. Hash operators build tables here.
get_next	Data fetch time. Called repeatedly until complete.

Important: time is cumulative. Current operator time = current time - child time.

ADVICE Section

System-generated suggestions:

• Missing indexes: Table xxx misses bitmap index
• Missing statistics: Table xxx Miss Stats! please run 'analyze xxx';
• Data skew: shuffle data skew! max rows is X, min rows is Y

Cost Breakdown

Metric	Description
Total cost	Query total time (ms)
Optimizer cost	QO plan generation time
Start query cost	Pre-execution init (schema sync, locking)
Get the first block cost	Time to first record batch
Get result cost	Time to all results

Resource Consumption

Format: 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 (cache miss)
Read bytes	Total reads (disk + cache)

Common Operators

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

Scan Operators

Operator	Description
Seq Scan	Full table scan
Index Scan using Clustering_index	Column-store index scan
Index Seek (pk_index)	Row-store primary key scan

Filter Operators

Operator	Description
Filter	No index hit — add indexes
Segment Filter	Segment key hit
Cluster Filter	Clustering key hit
Bitmap Filter	Bitmap index hit

Data Movement

Operator	Description
Local Gather	Merge files within shard
Gather	Merge shards to final result
Redistribution	Data shuffle — check distribution_key
Broadcast	Small table broadcast to all shards

Join Operators

Operator	Description
Hash Join	Hash-based join (ensure small table is hash table)
Nested Loop	Nested loop join (avoid for large data)
Cross Join	Optimized non-equi join (V3.0+)

Aggregation

Operator	Description
HashAggregate	Hash-based aggregation
Partial/Final HashAggregate	Multi-stage aggregation

Other

Operator	Description
Sort	ORDER BY
Limit	Row limit (check if pushed to scan)
ExecuteExternalSQL	PQE execution — rewrite for HQE

Optimization Workflow

1. Run EXPLAIN ANALYZE on slow query
2. Check ADVICE section for immediate fixes
3. Identify bottleneck operators (highest time)
4. Apply targeted optimizations:

Issue	Symptom	Solution
Missing stats	rows=1000	ANALYZE <table>
Data shuffle	Redistribution	Fix distribution_key
Wrong hash table	Large table as hash	Update statistics
No index	Filter only	Add clustering/bitmap index
PQE execution	ExecuteExternalSQL	Rewrite to HQE functions
Data skew	Large max/min variance	Review distribution

Key GUC Parameters

-- Multi-stage aggregation
SET optimizer_force_multistage_agg = on;

-- Join order control (for complex multi-table joins)
SET optimizer_join_order = 'query';  -- Follow SQL order
SET optimizer_join_order = 'greedy'; -- Greedy algorithm

-- Disable Cross Join
SET hg_experimental_enable_cross_join_rewrite = off;

To persist these settings at database level, use the CLI:

hologres guc set optimizer_force_multistage_agg on
hologres guc set optimizer_join_order query

Best Practices

1. Always use EXPLAIN ANALYZE for production analysis
2. Run ANALYZE after significant data changes
3. Design distribution_key based on JOIN/GROUP BY patterns
4. Set clustering_key for range query columns
5. Use bitmap indexes for low-cardinality filters
6. Ensure small table is hash table in joins
7. Avoid non-equi joins when possible
8. Rewrite PQE functions to HQE alternatives

Reference Links

Reference	Description
references/operators.md	Detailed operator descriptions
references/optimization-patterns.md	Common optimization patterns
references/guc-parameters.md	Query tuning parameters