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

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.

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]

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

Resource Consumption

Format: total(max_worker/avg_worker/min_worker)

Common Operators

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

Scan Operators

Filter Operators

Data Movement

Join Operators

Aggregation

Other

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:

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