AnalyticDB:Acelerar consultas en tablas orientadas a columnas mediante claves de ordenación e índices aproximados

Contexto

Al crear una tabla, puede definir una o más columnas como claves de ordenación (SORTKEY). Una vez que se escriben los datos en la tabla, esta se puede ordenar y reorganizar según dichas claves.

Ordenar una tabla acelera las consultas con restricciones de rango. La base de datos registra los valores mínimo y máximo de cada columna en cada grupo fijo de filas. Cuando una consulta utiliza predicados de rango, el motor de consultas de ADBPG aprovecha estos valores para omitir rápidamente los bloques de datos que no cumplen los predicados durante un escaneo de tabla (SCAN).

Por ejemplo, suponga que una tabla almacena siete años de datos en orden cronológico. Para consultar los datos de un mes, solo se necesita escanear 1/(7*12) de la información. Es decir, se omite el 98.8 % de los bloques de datos durante el escaneo. Sin embargo, si los datos no están ordenados por tiempo, podría ser necesario escanear todos los bloques del disco.

• **Comparación de rendimiento**

  • Esta sección toma como ejemplo la mejora de rendimiento que aporta la ordenación compuesta a los índices aproximados. Así se demuestra que los índices aproximados superan a los escaneos completos de tabla.

    Se utiliza como ejemplo una tabla Lineitem de TPC-H que almacena siete años de datos. Se compara el rendimiento de las consultas con restricciones de rango en dos casos: cuando los datos no están ordenados por la columna l_shipdate y cuando l_shipdate se usa como clave de ordenación con los datos ya ordenados.

    Nota

    La implementación de TPC en este tema se deriva del TPC Benchmark y no es comparable con los resultados publicados de dicho estándar. Las pruebas aquí descritas no cumplen con todos los requisitos del TPC Benchmark.

    Procedimiento de prueba

    :

    1. Cree una instancia de 32 nodos.

    2. Escriba 13 mil millones de filas en la tabla Lineitem.

    3. Consulte los datos del periodo comprendido entre 1997-09-01 y 1997-09-30.

       • Los datos no están ordenados por l_shipdate. Una consulta de rango tarda aproximadamente 34.6 segundos:

         adbpgadmin=# select count(*) from lineitem where l_shipdate between '1997-09-01' and '1997-09-30';
            count
         -----------
          149619638
         (1 row)
         Time: 34651.793 ms

       • Los datos están ordenados por l_shipdate.

         adbpgadmin=# select count(*) from lineitem where l_shipdate between '1997-09-01' and '1997-09-30';
            count
         -----------
          149619638
         (1 row)
         Time: 1661.046 ms

Definir claves de ordenación al crear una tabla

Ejemplo

create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) ORDER BY (volume);

Sintaxis

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type  ...} ]
)
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ ORDER BY (column, [ ... ] )]

Para versiones del kernel de la base de datos anteriores a 20210326, utilice la siguiente sintaxis para especificar las claves de ordenación: SORTKEY (column, [ ... ])

Ordenar una tabla

• **Ordenación compuesta**

  • SORT [tablename]

  • Para versiones del kernel de la base de datos anteriores a 20210326, ejecute el siguiente comando:

    VACUUM SORT ONLY [tablename]

• **Ordenación intercalada**

  • MULTISORT [tablename]

  • Para versiones del kernel de la base de datos anteriores a 20210326, ejecute el siguiente comando:

    VACUUM REINDEX [tablename]

Después de ejecutar SORT o MULTISORT en una tabla, los datos actuales se organizan en un orden global definido por las claves de ordenación. No obstante, conforme se escriben nuevos datos de forma continua, la porción sin ordenar crece constantemente. Esto puede afectar el rendimiento del filtrado por índices aproximados. Por lo tanto, debe ejecutar periódicamente SORT o MULTISORT para volver a ordenar la tabla y mantener el rendimiento del filtrado.

Modificar claves de ordenación

Puede modificar las claves de ordenación de una tabla orientada a columnas existente según los cambios del negocio. La sintaxis del comando es la siguiente:

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name SET ORDER BY (column, [ ... ] )

Este comando solo modifica el catálogo y no ordena los datos de inmediato. Debe ejecutar el comando SORT table_name para ordenarlos.

Ejemplo

ALTER TABLE test SET ORDER BY(high,low);

Para versiones del kernel de la base de datos anteriores a 20210326, ejecute el siguiente comando:

ALTER TABLE test SET SORTKEY(high,low);

Cómo elegir las claves y los métodos de ordenación

Si sus instrucciones SQL de consulta incluyen frecuentemente predicados de igualdad o de rango sobre una o más columnas, como una columna de tiempo, utilícelas como claves de ordenación. De esta manera, los datos ordenados y los índices aproximados acelerarán las consultas.

En general, se recomienda la ordenación compuesta, ya que la intercalada requiere organización adicional de datos durante el proceso. La ordenación intercalada con VACUUM REINDEX tarda más que la ordenación compuesta con VACUUM SORT ONLY.

Si los predicados de sus consultas no siempre incluyen ciertas columnas, utilice la ordenación intercalada para acelerarlas. Este método admite un máximo de 8 columnas.

Comparación de rendimiento: ordenación compuesta frente a intercalada

Se aplicaron ambos métodos de ordenación a la misma tabla para comparar su impacto en el rendimiento de distintas consultas y escenarios.

En este escenario se crea una tabla llamada test con 4 columnas (id, num1, num2 y value), donde (id, num1, num2) funciona como clave de ordenación. La tabla contiene 10 millones de registros. Aunque no es un volumen particularmente grande para ADBPG, permite observar la diferencia de rendimiento entre ambos métodos. Esta diferencia se vuelve más evidente con conjuntos de datos mayores.

• **Crear las tablas de prueba y establecer las claves de ordenación**

  • CREATE TABLE test(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    ORDER BY(id, num1, num2);
    CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    ORDER BY(id, num1, num2);

• **Escribir 10 millones de filas de datos**

  • INSERT INTO test(id, num1, num2, value) select g, 
    (random()*10000000)::int, 
    (random()*10000000)::int,
    (array['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 +1)]
    FROM generate_series(1, 10000000) as g;
    INSERT INTO test_multi SELECT * FROM test;
    adbpgadmin=# SELECT count(*) FROM test;
      count
    ----------
     10000000
    (1 row)
    adbpgadmin=# SELECT count(*) FROM test_multi;
      count
    ----------
     10000000
    (1 row)

• **Aplicar la ordenación compuesta y la intercalada a ambas tablas**

  • SORT test;
    MULTISORT test_multi;

• **Comparación de rendimiento en consultas puntuales**

  • • Predicados sobre la primera columna de la clave de ordenación.

      -- Q1: predicates on the leading column
      select * from test where id = 100000;
      select * from test_multi where id = 100000;

    • Predicados sobre la segunda columna.

      -- Q2: predicates on the second column
      select * from test where num1 = 8766963;
      select * from test_multi where num1 = 8766963;

    • Predicados sobre la segunda y tercera columnas.

      -- Q3: predicates on the second and third columns
      select * from test where num1 = 100000 and num2=2904114;
      select * from test_multi where num1 = 100000 and num2=2904114;

    <!--@div {"id":"table_rrw_xco_4so","data-tag="table"}-->

    Tabla 1. Resultados de la comparación de rendimiento

    Método de ordenación	Q1	Q2	Q3
    Ordenación compuesta	0,026s	3.95s	4.21s
    Ordenación intercalada	0.55s	0.42s	0,071s

• **Comparación de rendimiento en consultas de rango**

  • • Predicados sobre la primera columna de la clave de ordenación.

      -- Q1: predicates on the leading column
      select count(*) from test where id>5000 and id < 100000;
      select count(*) from test_multi where id>5000 and id < 100000;

    • Predicados sobre la segunda columna.

      -- Q2: predicates on the second column
      select count(*) from test where num1 >5000 and num1 <100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000;

    • Predicados sobre la segunda y tercera columnas.

      -- Q3: predicates on the second and third columns
      select count(*) from test where num1 >5000 and num1 <100000; and num2 < 100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000 and num2 < 100000;

    Tabla 2. Resultados de la comparación de rendimiento

    Método de ordenación	Q1	Q2	Q3
    Ordenación compuesta	0.07s	3.35s	3.64s
    Ordenación intercalada	0.44s	0.28s	0,047s

• **Conclusión**

  • • En el escenario Q1, la ordenación compuesta ofrece un excelente rendimiento porque los predicados incluyen la primera columna de la clave de ordenación, mientras que la intercalada presenta un desempeño ligeramente inferior.

    • En el escenario Q2, la ordenación compuesta resulta prácticamente ineficaz porque los predicados no incluyen la primera columna de la clave de ordenación. En cambio, la intercalada mantiene una mejora de rendimiento estable.

    • En el escenario Q3, la ordenación compuesta sigue siendo ineficaz por la ausencia de la primera columna de la clave de ordenación en los predicados. Además, las condiciones adicionales generan sobrecarga de comparación, lo que alarga el tiempo de la consulta. La ordenación intercalada muestra un mejor rendimiento, ya que cuantas más columnas de la clave incluyan los predicados, mayor será la mejora.