Hologres:Best practices for optimizing query performance

Check if statistics are current

Run EXPLAIN on your query and check the rows estimate for each table.

If a large table shows rows=1000 (the default), the statistics are outdated.

Update statistics

analyze <tablename>;

Identify when to update statistics

Run the analyze <tablename> command in the following situations.

• After importing data.

• After multiple INSERT, UPDATE, or DELETE operations.

• For both internal and foreign tables.

• On parent tables for partitioned tables.

In addition, if you encounter OOM errors during joins or slow query performance, run analyze <tablename> before you run the import task to optimize efficiency.

Understand default shard count

Hologres sets a default shard count based on instance specifications, approximately equal to available query CUs. After instance scaling, existing databases retain their original shard count—only new databases use the updated default.

Update statistics for join queries

As mentioned in Maintain statistics, outdated statistics may cause the larger table to create a hash table, reducing join efficiency. Run ANALYZE to update table statistics.

Select distribution keys for local joins

Distribution keys determine how data is distributed across shards. Proper selection enables local joins and reduces data shuffling.

-- Create tables with matching distribution keys
BEGIN;
CREATE TABLE orders (order_id INT, customer_id INT, amount DECIMAL);
CALL set_table_property('orders', 'distribution_key', 'customer_id');
COMMIT;

BEGIN;
CREATE TABLE customers (id INT, name TEXT);
CALL set_table_property('customers', 'distribution_key', 'id');
COMMIT;

After setting distribution keys correctly, the execution plan shows no Redistribute Motion operator, indicating local joins without data shuffling.

Use Runtime Filter in joins

Starting from V2.0, Hologres automatically applies Runtime Filter optimization for large-small table joins. This reduces scanned data and improves join performance without manual configuration.

Tune join order algorithms

For complex queries with many tables, the optimizer may spend significant time finding the optimal join order. Adjust the algorithm if optimization time is excessive:

set optimizer_join_order = '<value>'; 

Optimize Motion operators in joins

Hologres uses Motion operators to redistribute data between shards:

Check execution plans for costly Motion operators and adjust table design accordingly:

• Time-consuming Motion operators: Redesign the distribution.

• Inefficient Motion characters caused by outdated statistics: Refresh statistics with analyze.

• Broadcasting small tables: Reduce shard counts to optimize Broadcast Motion efficiency.

Optimize COUNT DISTINCT

• Replace exact COUNT DISTINCT (resource-intensive) with APPROX_COUNT_DISTINCT (faster, 0.1% to 1% error rate) when slight variance is acceptable.

• Replace COUNT DISTINCT with UNIQ (V1.3+).

• Set an appropriate distribution key

  Set the Count Distinct key as the distribution key for multiple operations with the same key to avoid data shuffles.

• Upgrade to V2.1+ for built-in optimizations

  These versions include built-in optimizations for various COUNT DISTINCT scenarios: one or more COUNT DISTINCT, data skew, and no GROUP BY.

Choose storage formats

Hologres supports row, column, and hybrid storage. Select a storage class based on your business scenario:

Choose data types

• Use smaller types where possible (INT instead of BIGINT)

• Specify precision for DECIMAL/NUMERIC types.

• Avoid FLOAT or DOUBLE for GROUP BY columns.

• Use TEXT for versatility. Minimize N when using VARCHAR(N) or CHAR(N).

• Use TIMESTAMPTZ and DATE instead of TEXT for dates.

• Use consistent data types in join conditions to avoid implicit conversions

Design a primary key

Proper primary keys improve execution plans, especially for GROUP BY queries.

Setting a primary key in columnar store scenarios can slow down writes. Performance is typically 3x faster without a primary key.

Use a partitioned table

Choose proper indexes

Example:

BEGIN;
CREATE TABLE events (
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    event_type TEXT
);
CALL set_table_property('events', 'clustering_key', 'event_time');
CALL set_table_property('events', 'segment_key', 'event_time');
CALL set_table_property('events', 'bitmap_columns', 'user_id,event_type');
COMMIT;

Verify index usage in a query by running EXPLAIN:

EXPLAIN SELECT * FROM events WHERE event_time > '2026-01-01';

Disable dictionary encoding for character columns

Dictionary encoding speeds up string comparisons but adds encode/decode overhead. Disable it for columns where comparison cost is low:

BEGIN;
CREATE TABLE logs (id INT, message TEXT);
CALL set_table_property('logs', 'dictionary_encoding_columns', '');
COMMIT;

Avoid external SQL (Postgres) such as NOT IN

Hologres uses HQE (Hologres Query Engine) for optimal performance. When HQE doesn't support an operator, it falls back to PQE (Postgres Query Engine), reducing performance.

EXPLAIN SELECT * FROM orders WHERE id NOT IN (SELECT id FROM cancelled_orders);

select * from tmp where not exists (select a from tmp1 where a = tmp.a);

select name,unnest(string_to_array(age,',')) from demo;

select cast(substring(c1, 13, 2) as int) AS hour from t2;

select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;

select regexp_replace(c1::text,'-','0') from t2;

select replace(c1::text,'-','') from t2;

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')  at time zone 'utc') from t2

select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;

select cast(c1 as timestamp) from t2;

select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;

select c1::text from t2;

select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;

Avoid fuzzy LIKE queries

Avoid fuzzy searches like the LIKE operation as they do not use indexes.

Optimize ORDER BY LIMIT queries

Starting from V1.3, Hologres supports Merge Sort for ORDER BY ... LIMIT queries, eliminating extra sort operations.

Optimize GROUP BY queries

Set the GROUP BY column as the distribution key if operations are time-consuming to reduce data redistribution.

-- If data is distributed based on the values in column a, runtime data redistribution is reduced, and the parallel computing capability of shards is fully utilized.
select a, count(1) from t1 group by a; 

Starting from V4.0, Hologres automatically rewrites multiple related GROUP BY columns to reduce merges (max search depth: 5 layers). For example, a clause such as GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) is rewritten to the equivalent GROUP BY COL_A. For example:

CREATE TABLE tbl (
    a int,
    b int,
    c int
);

-- Query
SELECT
    a,
    a + 1 as a1,
    a + 2 as a2,
    sum(b)
FROM tbl
GROUP BY
    a,
    a1,
    a2;

The execution plan shows a query rewrite was performed, and the GROUP BY clause contains only column a.

Disable this feature using the following GUC parameter:

-- Disable the feature at the session level.
SET hg_experimental_remove_related_group_by_key = off; 

-- Disable the feature at the database level.
ALTER DATABASE <database_name> SET hg_experimental_remove_related_group_by_key = off; 

Enable CTE reuse (Beta)

When a CTE is referenced multiple times, enable CTE reuse to avoid recomputation (V1.3+):

SET optimizer_cte_inlining=off;