Hologres:Use clustering keys to accelerate TopN queries

Em uma tabela orientada a colunas, a propriedade clustering_key define a ordem de classificação física dos dados. Consultas ORDER BY ... LIMIT N com ordenação na mesma direção da chave de clustering leem apenas algumas linhas antes de retornar o resultado. Por outro lado, consultas com ordenação na direção oposta precisam varrer a tabela inteira e, em seguida, classificar os resultados. Em tabelas grandes ou com colunas extensas, essa operação tem um alto custo.

Com as varreduras reversas, o operador de varredura lê os dados na ordem física inversa à da chave de clustering. Assim, as consultas ORDER BY ... LIMIT N reversas leem um volume de dados tão pequeno quanto as consultas diretas, o que elimina a necessidade de varredura completa da tabela e do operador Sort.

Contexto

Limites

• Somente tabelas orientadas a colunas (orientation = 'column') são compatíveis. As colunas de ordenação devem ser a chave de clustering da tabela ou um prefixo dela.

• Apenas consultas no formato ORDER BY ... LIMIT N são aceleradas.

• Este recurso é voltado para consultas com ordenação na ordem inversa da chave de clustering. Consultas com ordenação na mesma ordem já se beneficiam da chave de clustering sem precisar deste recurso.

• Este recurso não altera a semântica de clustering_key nas definições de tabela nem o formato de armazenamento.

Quando uma consulta pode usar uma varredura reversa

Após a ativação do recurso, o otimizador usa uma varredura reversa apenas se a direção da ordenação e a posição de NULL na consulta corresponderem ao layout físico. No armazenamento do Hologres, os valores NULL são sempre posicionados no final físico (NULLS LAST). A tabela a seguir descreve as regras.

Em resumo, para colunas NOT NULL, o ORDER BY padrão em qualquer direção é acelerado sem nenhuma alteração no SQL. Para colunas que aceitam nulos, a posição de NULL na consulta deve corresponder ao layout físico. Para obter mais informações, consulte Table design recommendations e Behavior changes after upgrading to V5.0.0.

Ativar varreduras reversas

Use o parâmetro GUC hg_enable_reverse_scan_for_clustering_keys para ativar ou desativar esse recurso.

Ative o recurso em um dos seguintes níveis:

• Nível de sessão: aplica-se apenas à conexão atual. Use-o para testar o recurso ou para verificar uma única consulta.

  -- Enable
  SET hg_enable_reverse_scan_for_clustering_keys = on;
  -- Disable (restore the original behavior)
  SET hg_enable_reverse_scan_for_clustering_keys = off;

• Nível de banco de dados: persiste e aplica-se por padrão a todas as novas conexões ao banco de dados. Use-o para ativar o recurso em todo o banco de dados. É necessário ser superusuário ou proprietário do banco de dados para executar as seguintes instruções.

  -- Enable (existing connections are not affected; reconnect to apply)
  ALTER DATABASE <DB_NAME> SET hg_enable_reverse_scan_for_clustering_keys = on;
  -- Disable
  ALTER DATABASE <DB_NAME> SET hg_enable_reverse_scan_for_clustering_keys = off;
  -- Remove the database-level setting and restore the default value (off)
  ALTER DATABASE <DB_NAME> RESET hg_enable_reverse_scan_for_clustering_keys;

Verifique se uma consulta usa uma varredura reversa

Execute EXPLAIN ANALYZE e visualize o plano de execução. O principal indicador é verificar se o nó Seq Scan possui a anotação Order By: xxx:

• Anotação presente: a consulta é acelerada. O operador de varredura retorna linhas ordenadas e não é necessário um Sort acima dele.

• Anotação ausente: a consulta não é acelerada. O sistema executa um Sort extra acima da varredura.

Compare também scan_rows, que é o número de linhas lidas do armazenamento. Esse valor é muito menor que a contagem total de linhas da tabela quando a consulta é acelerada e próximo a ela quando não é.

O exemplo a seguir usa a tabela demo_single_notnull de Example. A tabela tem clustering_key='ts' e todas as colunas são NOT NULL. Execute a seguinte consulta reversa:

EXPLAIN ANALYZE SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;

• Com o recurso ativado (acelerado): o nó Seq Scan tem a anotação Order By: ts e não há nenhum Sort interno.

  ->  Seq Scan on demo_single_notnull
        Order By: ts                 ← Accelerated: the scan returns rows in sorted order
      [scan_rows=21760]              ← Only a few row groups are scanned

• Com o recurso desativado (não acelerado): o nó Seq Scan não tem a anotação Order By, há um nó Sort acima dele e scan_rows é igual à contagem de linhas da tabela.

  ->  Sort
        Sort Key: ts
        ->  Seq Scan on demo_single_notnull
            [scan_rows=1000000]      ← Full table scan

Recomendações de design de tabela

Para garantir que as consultas em ambas as direções sejam aceleradas de forma confiável, sem instruções SQL específicas para cada direção ou cláusulas NULLS, siga estas recomendações:

• Não especifique uma direção de ordenação para a chave de clustering (recomendado): especifique clustering_key = 'ts' em vez de 'ts:desc' ou 'ts:asc'. Se nenhuma direção for especificada, asc será usado. Depois de ativar hg_enable_reverse_scan_for_clustering_keys, tanto ORDER BY ts ASC LIMIT N quanto ORDER BY ts DESC LIMIT N são acelerados, e a aplicação não precisa se preocupar com a direção. Isso funciona porque o layout físico asc corresponde à ordenação padrão de valores NULL no PostgreSQL (ASC NULLS LAST e DESC NULLS FIRST), de modo que o ORDER BY padrão em qualquer direção é acelerado.

• Declare as colunas da chave de clustering como NOT NULL sempre que possível: como não há posição de NULL para corresponder, o ORDER BY padrão é acelerado em qualquer direção de consulta e para qualquer direção de chave de clustering, sem alterações no SQL.

• Evite combinar uma coluna que aceita nulos com a direção desc: nessa combinação, a posição padrão de NULL no PostgreSQL é o oposto do layout de armazenamento. A consulta é acelerada apenas se você adicionar explicitamente NULLS LAST (direta) ou NULLS FIRST (reversa), um detalhe fácil de esquecer. Se a tabela já usa esse design, adicione a cláusula NULLS correspondente à instrução SQL.

Mudanças de comportamento após a atualização para a V5.0.0

O Hologres V5.0.0 corrige um problema de corretude com colunas de chave de clustering que aceitam nulos. Nas versões anteriores, o otimizador considerava apenas a direção da ordenação e ignorava a ordenação de valores NULL ao decidir se deve usar a chave de clustering. Como resultado, linhas com valores NULL em colunas que aceitam nulos poderiam estar ausentes dos resultados. A partir da V5.0.0, a ordenação de valores NULL não é mais ignorada por padrão. Uma consulta é acelerada apenas se sua ordenação de valores NULL corresponder ao layout de armazenamento.

Impacto: após a atualização para a V5.0.0, algumas consultas em uma coluna de chave de clustering que aceita nulos podem deixar de usar a chave de clustering se a posição de NULL não corresponder ao layout de armazenamento. Por exemplo, uma consulta em uma coluna desc que omite NULLS LAST ou especifica explicitamente NULLS FIRST. Essas consultas recorrem a uma varredura completa da tabela seguida de um Sort e podem demorar mais. Esta é uma correção intencional de corretude, não um bug.

Para restaurar o comportamento anterior de ignorar a ordenação de valores NULL, use o seguinte parâmetro GUC:

-- Restore the earlier behavior of ignoring the NULL ordering
SET hg_ignore_clustering_key_null_order = on;

Exemplo

-- 1) Create a column-oriented table with a clustering key
CREATE TABLE demo_single_notnull (
    id      int  NOT NULL,
    ts      int  NOT NULL,
    payload text NOT NULL
) WITH (orientation='column', clustering_key='ts');

-- 2) Enable reverse scans
SET hg_enable_reverse_scan_for_clustering_keys = on;

-- 3) Run a reverse query: the clustering key is ts, and the query returns the 1,000 rows with the largest ts values
SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;

-- 4) Verify that the query is accelerated
EXPLAIN ANALYZE SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;
-- The query is accelerated if the Seq Scan node has an "Order By: ts" annotation and scan_rows is far smaller than the table row count.

Referências

• Clustering key

• GUC parameters

• EXPLAIN and EXPLAIN ANALYZE