Hologres:Optimize internal table 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 analyze <tablename> when:

• After importing data.

• After multiple INSERT, UPDATE, or DELETE operations.

• For both internal and foreign tables.

• On parent tables for partitioned tables.

If you encounter OOM errors during joins or slow queries, run analyze <tablename> before importing data.

Understand default shard count

Hologres sets a default shard count based on instance specifications, roughly equal to available query CUs. After scaling, existing databases keep 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;

With correct distribution keys, the execution plan shows no Redistribute Motion operator, confirming local joins.

Use Runtime Filter in joins

From V2.0, Hologres automatically applies Accelerate multi-table joins with runtime filters for large-small table joins, reducing scanned data without manual configuration.

Tune join order algorithms

For queries joining many tables, the optimizer may take too long to find the optimal join order. Adjust the algorithm as needed:

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:

• 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

  Use the COUNT DISTINCT key as the distribution key to avoid data shuffles across shards.

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

  V2.1+ includes built-in optimizations for COUNT DISTINCT scenarios including one or more COUNT DISTINCT, data skew, and queries without GROUP BY.

Choose storage formats

Hologres supports row, column, and hybrid storage. Choose based on your workload:

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.

In columnar store mode, primary keys slow writes—throughput is typically 3x higher without one.

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 best performance. Unsupported operators fall back to PQE (Postgres Query Engine), which is slower.

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

From V1.3, Hologres supports Merge Sort for ORDER BY ... LIMIT queries, eliminating redundant sort operations.

Optimize GROUP BY queries

Set the GROUP BY column as the distribution key 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; 

From V4.0, Hologres automatically rewrites related GROUP BY columns to reduce merges (max search depth: 5 layers). A clause like GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) is rewritten to GROUP BY COL_A. 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 confirms the rewrite—the GROUP BY clause contains only column a.

QUERY PLAN
Gather  (cost=0.00..5.00 rows=1 width=20)
  -> Project  (cost=0.00..5.00 rows=1 width=20)
    -> HashAggregate  (cost=0.00..5.00 rows=1 width=12)
          Group Key: a
        -> Redistribution  (cost=0.00..5.00 rows=1 width=8)
              Hash Key: a
            -> Local Gather  (cost=0.00..5.00 rows=1 width=8)
              -> Seq Scan on tbl  (cost=0.00..5.00 rows=1 width=8)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

To disable:

-- 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

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

SET optimizer_cte_inlining=off;

Optimize Top-N analysis

• In OLAP scenarios, retrieving the top N records within a group is a common requirement. For example, the following SQL query retrieves the top two records from the t table within each b partition, sorted by a:

  CREATE TABLE t (
    a int,
    b int
  );
  
  INSERT INTO t VALUES (2, 1), (3, 1), (4, 1), (5, 2), (6, 2);
  
  SELECT
      *
  FROM (
    SELECT
    a,
    b,
    row_number() OVER (PARTITION BY b ORDER BY a) AS rn
    FROM
    t) t1
  WHERE
      rn <= 2;

  The execution result is as follows:

  a	b	rn
  5	2	1
  6	2	2
  2	1	1
  3	1	2

• From Hologres V4.1, the Partition Sort operator pushes the LIMIT clause into the Partition, filtering data early during sorting. This reduces memory for window functions like row_number and rank in Top-N scenarios, lowering OOM risk. Enabled by default. To disable:

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