Hologres:Optimize internal table query performance

Vérifier si les statistiques sont à jour

Exécutez EXPLAIN sur votre requête et vérifiez l'estimation rows pour chaque table.

Si une grande table affiche rows=1000 (la valeur par défaut), les statistiques sont obsolètes.

Mettre à jour les statistiques

analyze <tablename>;

Identifier quand mettre à jour les statistiques

Exécutez analyze <tablename> lorsque :

• Après l'importation de données.

• Après plusieurs opérations INSERT, UPDATE ou DELETE.

• Pour les tables internes et les tables externes.

• Sur les tables parentes pour les tables partitionnées.

Si vous rencontrez des erreurs OOM lors de jointures ou des requêtes lentes, exécutez analyze <tablename> avant d'importer les données.

Comprendre le nombre de shards par défaut

Hologres définit un nombre de shards par défaut basé sur les spécifications de l'instance, approximativement égal aux CUs de requête disponibles. Après un scaling, les bases de données existantes conservent leur nombre de shards d'origine — seules les nouvelles bases de données utilisent la nouvelle valeur par défaut.

Déterminer quand ajuster le nombre de shards

• Après un scale-up d'un facteur 5 ou plus : Créez un nouveau Table Group avec un nombre de shards plus élevé.

• Pour de nouvelles charges de travail métier : Créez un nouveau Table Group avec un nombre de shards approprié.

• En cas de problèmes de parallélisme : Vérifiez si le nombre total de shards dépasse la valeur par défaut recommandée.

Mettre à jour les statistiques pour les requêtes de jointure

Comme mentionné dans Maintenir les statistiques, des statistiques obsolètes peuvent amener la table la plus grande à créer une table de hachage, réduisant ainsi l'efficacité de la jointure. Exécutez ANALYZE pour mettre à jour les statistiques de la table.

Sélectionner les clés de distribution pour les jointures locales

Les clés de distribution déterminent la manière dont les données sont réparties entre les shards. Une sélection appropriée permet des jointures locales et réduit le brassage de données.

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

Avec des clés de distribution correctes, le plan d'exécution n'affiche aucun opérateur Redistribute Motion, confirmant ainsi les jointures locales.

Utiliser Runtime Filter dans les jointures

À partir de la V2.0, Hologres applique automatiquement Accelerate multi-table joins with runtime filters pour les jointures entre grandes et petites tables, réduisant ainsi le volume de données analysées sans configuration manuelle.

Ajuster les algorithmes d'ordre de jointure

Pour les requêtes joignant de nombreuses tables, l'optimiseur peut mettre trop de temps à trouver l'ordre de jointure optimal. Ajustez l'algorithme selon vos besoins :

set optimizer_join_order = '<value>'; 

Optimiser les opérateurs Motion dans les jointures

Hologres utilise des opérateurs Motion pour redistribuer les données entre les shards :

Vérifiez les plans d'exécution pour identifier les opérateurs Motion coûteux et ajustez la conception de la table :

• Opérateurs Motion chronophages : Redéfinissez la distribution.

• Caractéristiques Motion inefficaces causées par des statistiques obsolètes : Actualisez les statistiques avec analyze.

• Diffusion de petites tables : Réduisez le nombre de shards pour optimiser l'efficacité du Broadcast Motion.

Optimiser COUNT DISTINCT

• Remplacez COUNT DISTINCT exact (gourmand en ressources) par APPROX_COUNT_DISTINCT (plus rapide, taux d'erreur de 0,1 % à 1 %) lorsqu'une légère variance est acceptable.

• Remplacez COUNT DISTINCT par UNIQ (V1.3+).

• Définissez une clé de distribution appropriée

  Utilisez la clé COUNT DISTINCT comme clé de distribution pour éviter le brassage de données entre les shards.

• Mettez à niveau vers la V2.1+ pour bénéficier des optimisations intégrées

  La V2.1+ inclut des optimisations intégrées pour les scénarios COUNT DISTINCT, y compris un ou plusieurs COUNT DISTINCT, le déséquilibre des données et les requêtes sans GROUP BY.

-- Create a test table.
CREATE TABLE tbl(x int4, y int4);

-- Insert test data.
INSERT INTO tbl VALUES (1,2), (null,200), (1000,null), (10000,20000);

-- Query data
SELECT
    sum(x + 1),
    sum(x + 2),
    sum(x - 3),
    sum(x - 4)
FROM
    tbl;

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

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

Choisir les formats de stockage

Hologres prend en charge le stockage par ligne, par colonne et hybride. Choisissez en fonction de votre charge de travail :

Choisir les types de données

• Utilisez des types plus petits lorsque cela est possible (INT au lieu de BIGINT)

• Spécifiez la précision pour les types DECIMAL/NUMERIC.

• Évitez FLOAT ou DOUBLE pour les colonnes GROUP BY.

• Utilisez TEXT pour sa polyvalence. Minimisez N lorsque vous utilisez VARCHAR(N) ou CHAR(N).

• Utilisez TIMESTAMPTZ et DATE au lieu de TEXT pour les dates.

• Utilisez des types de données cohérents dans les conditions de jointure pour éviter les conversions implicites

Concevoir une clé primaire

Des clés primaires appropriées améliorent les plans d'exécution, en particulier pour les requêtes GROUP BY.

En mode de stockage columnaire, les clés primaires ralentissent les écritures — le débit est généralement 3 fois plus élevé sans clé primaire.

Utiliser une table partitionnée

Choisir des index appropriés

Exemple :

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;

Vérifiez l'utilisation des index dans une requête en exécutant EXPLAIN :

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

Désactiver l'encodage par dictionnaire pour les colonnes de caractères

L'encodage par dictionnaire accélère les comparaisons de chaînes mais ajoute une surcharge d'encodage/décodage. Désactivez-le pour les colonnes où le coût de comparaison est faible :

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

Éviter le SQL externe (Postgres) tel que NOT IN

Hologres utilise HQE (Hologres Query Engine) pour de meilleures performances. Les opérateurs non pris en charge basculent vers PQE (Postgres Query Engine), qui est plus lent.

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;

Éviter les requêtes floues LIKE

Évitez les recherches floues comme l'opération LIKE car elles n'utilisent pas les index.

Optimiser les requêtes ORDER BY LIMIT

À partir de la V1.3, Hologres prend en charge Merge Sort pour les requêtes ORDER BY ... LIMIT, éliminant ainsi les opérations de tri redondantes.

Optimiser les requêtes GROUP BY

Définissez la colonne GROUP BY comme clé de distribution pour réduire la redistribution des données.

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

À partir de la V4.0, Hologres réécrit automatiquement les colonnes GROUP BY connexes pour réduire les fusions (profondeur de recherche maximale : 5 niveaux). Une clause telle que GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) est réécrite en GROUP BY COL_A. Exemple :

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;

Le plan d'exécution confirme la réécriture — la clause GROUP BY ne contient que la colonne 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

Pour désactiver :

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

Activer la réutilisation des CTE

Lorsqu'un CTE est référencé plusieurs fois, activez la réutilisation des CTE pour éviter le recalcul (V1.3+) :

SET optimizer_cte_inlining=off;

Optimiser l'analyse Top-N

• Dans les scénarios OLAP, la récupération des N premiers enregistrements au sein d'un groupe est une exigence courante. Par exemple, la requête SQL suivante récupère les deux premiers enregistrements de la table t dans chaque partition b, triés par 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;

  Le résultat d'exécution est le suivant :

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

• À partir de Hologres V4.1, l'opérateur Partition Sort pousse la clause LIMIT dans la Partition, filtrant les données tôt pendant le tri. Cela réduit la mémoire nécessaire pour les fonctions de fenêtre comme row_number et rank dans les scénarios Top-N, réduisant ainsi le risque d'OOM. Activé par défaut. Pour désactiver :

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

Une distribution inégale des données ralentit les requêtes. Vérifiez le nombre de lignes par shard pour détecter le déséquilibre :

-- hg_shard_id is a built-in hidden column in each table that describes the shard where the corresponding row of data is located.
SELECT hg_shard_id, count(1) FROM t1 GROUP BY hg_shard_id;

Si certains shards ont significativement plus de lignes que d'autres :

• Modifiez la distribution_key vers une colonne ayant une distribution de données uniforme.

  Important

  La modification de la clé de distribution nécessite la recréation de la table et la réimportation des données.

• Si les données sont intrinsèquement déséquilibrées, optimisez d'un point de vue métier

set hg_experimental_enable_result_cache = off;