Hologres:Optimize internal table query performance

Comprobar si las estadísticas están actualizadas

Ejecute EXPLAIN en su consulta y revise la estimación de rows para cada tabla.

Si una tabla grande muestra rows=1000 (el valor predeterminado), las estadísticas están desactualizadas.

Actualizar estadísticas

analyze <tablename>;

Identificar cuándo actualizar estadísticas

Ejecute analyze <tablename> en los siguientes casos:

• Después de importar datos.

• Después de múltiples operaciones INSERT, UPDATE o DELETE.

• Tanto para tablas internas como externas.

• En tablas padre para tablas particionadas.

Si encuentra errores OOM durante uniones o consultas lentas, ejecute analyze <tablename> antes de importar datos.

Entender el recuento predeterminado de fragmentos

Hologres establece un recuento predeterminado de fragmentos según las especificaciones de la instancia, aproximadamente igual a las CU de consulta disponibles. Después de escalar, las bases de datos existentes conservan su recuento original de fragmentos; solo las nuevas bases de datos utilizan el valor predeterminado actualizado.

Determinar cuándo ajustar el recuento de fragmentos

• Después de escalar 5 veces o más: Cree un nuevo Table Group con un mayor recuento de fragmentos.

• Para nuevas cargas de trabajo empresariales: Cree un nuevo Table Group con un recuento de fragmentos adecuado.

• Cuando experimente problemas de paralelismo: Compruebe si el total de fragmentos supera el valor predeterminado recomendado.

Actualizar estadísticas para consultas de unión

Como se menciona en Maintain statistics, las estadísticas desactualizadas pueden provocar que la tabla más grande cree una tabla hash, lo que reduce la eficiencia de la unión. Ejecute ANALYZE para actualizar las estadísticas de la tabla.

Seleccionar claves de distribución para uniones locales

Las claves de distribución determinan cómo se distribuyen los datos entre los fragmentos. Una selección adecuada permite uniones locales y reduce la redistribución de datos.

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

Con las claves de distribución correctas, el plan de ejecución no muestra el operador Redistribute Motion, lo que confirma que se realizan uniones locales.

Uso de Runtime Filter en uniones

A partir de la versión V2.0, Hologres aplica automáticamente Accelerate multi-table joins with runtime filters para uniones de tablas grandes y pequeñas, lo que reduce los datos examinados sin necesidad de configuración manual.

Ajustar algoritmos de orden de unión

En consultas que unen muchas tablas, el optimizador puede tardar demasiado en encontrar el orden de unión óptimo. Ajuste el algoritmo según sea necesario:

set optimizer_join_order = '<value>'; 

Optimizar operadores Motion en uniones

Hologres utiliza operadores Motion para redistribuir datos entre fragmentos:

Revise los planes de ejecución en busca de operadores Motion costosos y ajuste el diseño de la tabla:

• Operadores Motion que consumen mucho tiempo: Rediseñe la distribución.

• Caracteres Motion ineficientes causados por estadísticas desactualizadas: Actualice las estadísticas con analyze.

• Difusión de tablas pequeñas: Reduzca el recuento de fragmentos para optimizar la eficiencia de Broadcast Motion.

Optimizar COUNT DISTINCT

• Reemplace el COUNT DISTINCT exacto (intensivo en recursos) por APPROX_COUNT_DISTINCT (más rápido, tasa de error del 0.1% al 1%) cuando sea aceptable una ligera variación.

• Reemplace COUNT DISTINCT por UNIQ (V1.3+).

• Establezca una clave de distribución adecuada.

  Utilice la clave de COUNT DISTINCT como clave de distribución para evitar la redistribución de datos entre fragmentos.

• Actualice a V2.1+ para obtener optimizaciones integradas.

  La versión V2.1+ incluye optimizaciones integradas para escenarios de COUNT DISTINCT, incluidos uno o más COUNT DISTINCT, sesgo de datos y consultas sin 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; 

Elección de formatos de almacenamiento

Hologres admite almacenamiento por filas, por columnas e híbrido. Elija según su carga de trabajo:

Elección de tipos de datos

• Utilice tipos más pequeños siempre que sea posible (INT en lugar de BIGINT).

• Especifique la precisión para los tipos DECIMAL/NUMERIC.

• Evite FLOAT o DOUBLE para columnas de GROUP BY.

• Utilice TEXT por su versatilidad. Minimice N al usar VARCHAR(N) o CHAR(N).

• Utilice TIMESTAMPTZ y DATE en lugar de TEXT para fechas.

• Utilice tipos de datos consistentes en las condiciones de unión para evitar conversiones implícitas.

Diseño de una clave primaria

Las claves primarias adecuadas mejoran los planes de ejecución, especialmente para consultas GROUP BY.

En el modo de almacenamiento por columnas, las claves primarias ralentizan las escrituras; el rendimiento suele ser 3 veces mayor sin ellas.

Uso de tablas particionadas

Elección de índices adecuados

Ejemplo:

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;

Verifique el uso de índices en una consulta ejecutando EXPLAIN:

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

Deshabilitar la codificación de diccionario para columnas de caracteres

La codificación de diccionario acelera las comparaciones de cadenas, pero añade sobrecarga de codificación/decodificación. Deshabilítela para columnas donde el costo de comparación sea bajo:

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

Evitar SQL externo (Postgres) como NOT IN

Hologres utiliza HQE (Hologres Query Engine) para obtener el mejor rendimiento. Los operadores no compatibles recurren a PQE (Postgres Query Engine), que es más lento.

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;

Evitar consultas LIKE difusas

Evite búsquedas difusas como la operación LIKE, ya que no utilizan índices.

Optimizar consultas ORDER BY LIMIT**

A partir de la versión V1.3, Hologres admite Merge Sort para consultas ORDER BY ... LIMIT, lo que elimina operaciones de ordenamiento redundantes.

Optimizar consultas GROUP BY

Establezca la columna GROUP BY como clave de distribución para reducir la redistribución de datos.

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

A partir de la versión V4.0, Hologres reescribe automáticamente columnas GROUP BY relacionadas para reducir fusiones (profundidad máxima de búsqueda: 5 niveles). Una cláusula como GROUP BY COL_A, ((COL_A + 1)), ((COL_A + 2)) se reescribe a GROUP BY COL_A. Ejemplo:

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;

El plan de ejecución confirma la reescritura: la cláusula GROUP BY contiene únicamente la columna 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

Para deshabilitarlo:

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

Habilitar la reutilización de CTE

Cuando se hace referencia a un CTE varias veces, habilite la reutilización de CTE para evitar recálculos (V1.3+):

SET optimizer_cte_inlining=off;

Optimizar análisis Top-N

• En escenarios OLAP, recuperar los primeros N registros dentro de un grupo es un requisito común. Por ejemplo, la siguiente consulta SQL recupera los dos primeros registros de la tabla t dentro de cada partición b, ordenados por 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;

  El resultado de la ejecución es el siguiente:

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

• A partir de Hologres V4.1, el operador Partition Sort introduce la cláusula LIMIT en la Partition, filtrando datos tempranamente durante la clasificación. Esto reduce la memoria para funciones de ventana como row_number y rank en escenarios Top-N, disminuyendo el riesgo de OOM. Habilitado de forma predeterminada. Para deshabilitarlo:

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

Una distribución desigual de datos ralentiza las consultas. Compruebe el recuento de filas por fragmento para detectar sesgos:

-- 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 algunos fragmentos tienen significativamente más filas que otros:

• Cambie la distribution_key a una columna con distribución de datos uniforme.

  Importante

  Cambiar la clave de distribución requiere recrear la tabla y volver a importar los datos.

• Si los datos tienen un sesgo inherente, optimice desde una perspectiva empresarial.

set hg_experimental_enable_result_cache = off;