AnalyticDB:Acelere consultas em tabelas orientadas a colunas usando chaves de ordenação e índices aproximados

Escolha um método de ordenação

Use estas diretrizes para selecionar o método adequado aos seus padrões de consulta:

• Se a maioria das consultas filtra pela coluna principal da chave de ordenação, use a ordenação composta. Ela produz os tempos de resposta mais rápidos para predicados na coluna principal. Observe que a reordenação com ordenação composta leva mais tempo do que com a ordenação intercalada, pois realiza análises adicionais nos dados.

• Se as consultas filtram por colunas não principais ou por subconjuntos arbitrários da chave de ordenação, use a ordenação intercalada. Uma chave de ordenação intercalada suporta até oito colunas. Quanto mais colunas da chave de ordenação uma consulta referenciar, maior será o benefício de desempenho.

• Em caso de dúvida, comece com a ordenação composta. É a escolha mais simples e oferece melhor desempenho quando as consultas possuem uma coluna de filtro principal clara.

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

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

SORT table_name;

MULTISORT table_name;

ALTER TABLE table_name SET ORDER BY (column [, ...]);

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

Benchmark TPC-H: impacto da chave de ordenação em consultas de intervalo

Esta seção demonstra como a ordenação composta melhora o desempenho de consultas para índices de conjunto aproximado em comparação com uma varredura completa de tabela, usando uma tabela Lineitem do TPC-H que armazena sete anos de dados.

Esta implementação do TPC é derivada do TPC Benchmark e não é comparável aos resultados publicados do TPC Benchmark, pois esta implementação não atende a todos os requisitos do TPC Benchmark.

Configuração do teste:

1. Crie uma instância de 32 nós.

2. Grave 13 bilhões de linhas na tabela Lineitem.

3. Consulte dados no intervalo de tempo de 1997-09-01 a 1997-09-30, comparando os resultados quando os dados estão ordenados por l_shipdate versus não ordenados.

Composta versus intercalada: desempenho em diferentes formatos de consulta

O exemplo a seguir usa duas tabelas com dados e chaves de ordenação idênticos para mostrar como os dois métodos se comportam em diferentes formatos de consulta.

Configuração do teste:

• Duas tabelas (test e test_multi), cada uma com quatro colunas: id, num1, num2, value

• Chave de ordenação: (id, num1, num2) em ambas as tabelas

• 10 milhões de linhas por tabela

• test ordenada com ordenação composta (SORT test)

• test_multi ordenada com ordenação intercalada (MULTISORT test_multi)

Crie as tabelas e insira os dados:

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

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;

SORT test;
MULTISORT test_multi;

Desempenho de consultas pontuais

Todas as três consultas filtram pelas colunas da chave de ordenação, mas em posições diferentes.

-- Q1: filter on the leading column (id)
SELECT * FROM test WHERE id = 100000;
SELECT * FROM test_multi WHERE id = 100000;

-- Q2: filter on the second column (num1)
SELECT * FROM test WHERE num1 = 8766963;
SELECT * FROM test_multi WHERE num1 = 8766963;

-- Q3: filter on the second and third columns (num1, num2)
SELECT * FROM test WHERE num1 = 100000 AND num2 = 2904114;
SELECT * FROM test_multi WHERE num1 = 100000 AND num2 = 2904114;

Desempenho de consultas de intervalo

-- Q1: range filter on the leading column (id)
SELECT count(*) FROM test WHERE id > 5000 AND id < 100000;
SELECT count(*) FROM test_multi WHERE id > 5000 AND id < 100000;

-- Q2: range filter on the second column (num1)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000;

-- Q3: range filter on the second and third columns (num1, num2)
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;

Principais conclusões

• A ordenação composta vence na coluna principal. Os resultados da Q1 mostram que a ordenação composta tem um tempo de resposta menor do que a ordenação intercalada quando o filtro visa a primeira coluna da chave de ordenação.

• A ordenação intercalada vence em colunas não principais. Os resultados da Q2 e Q3 mostram que a ordenação intercalada supera significativamente a ordenação composta quando as consultas ignoram a coluna principal.

• A ordenação intercalada escala com o número de colunas. Quanto mais colunas da chave de ordenação uma consulta referenciar, maior será a vantagem de desempenho da ordenação intercalada (Q3 vs. Q2).

Este teste usa 10 milhões de linhas — um tamanho modesto para o AnalyticDB for PostgreSQL. A diferença de desempenho entre os dois métodos é mais pronunciada em tabelas maiores.