Isole gargalos em consultas

A otimização de consultas fica mais fácil quando você altera uma parte da consulta por vez e compara os resultados com uma base de referência estável. Este guia mostra como simplificar progressivamente uma consulta e usar as diferenças entre execuções para identificar quais operações mais contribuem para sua duração. Em seguida, você pode validar o possível gargalo antes de escolher uma otimização.

Comece com um padrão recorrente de consultas lentas que deseja investigar. Se ainda não identificou um, consulte Diagnosticar consultas lentas.

Para executar os exemplos deste guia conforme descritos, crie e carregue a tabela nyc_taxi.trips_small_inferred, caso ainda não tenha feito isso:

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

A tabela de exemplo usa ORDER BY (), portanto, seu filtro de data não pode usar uma chave de ordenação para eliminar dados durante a leitura. Use o exemplo para praticar o método de comparação, e não como referência de desempenho.

Simplificar uma consulta progressivamente permite comparar sua duração antes e depois de remover uma etapa do processamento. As diferenças ajudam a decidir se é necessário investigar a varredura e a filtragem, o agrupamento, os cálculos de agregação ou etapas posteriores, como a ordenação e a formatação da saída:

1. Execute a consulta original para estabelecer as medições de referência.
2. Mantenha GROUP BY, substitua os cálculos de agregação da consulta por count e remova operações posteriores, como a ordenação e a formatação da saída.
3. Remova o agrupamento e execute um count sem agrupamento para estimar o trabalho correspondente à varredura, à filtragem e a eventuais junções.

Essas etapas se aplicam diretamente a consultas de agregação agrupadas convencionais. Para consultas mais complexas, aplique o mesmo princípio a um bloco SELECT por vez: preserve fontes de dados e filtros equivalentes, remova uma operação por vez e verifique o plano de execução após cada alteração.

Use as práticas a seguir para tornar as medições comparáveis:

• Mantenha as cláusulas FROM, JOIN, PREWHERE e WHERE inalteradas para que todas as comparações usem os mesmos dados e intervalo de tempo.
• Execute cada versão da consulta várias vezes sob carga de sistema semelhante.
• Mantenha as condições de cache consistentes. Execute cada versão da consulta antes de registrar as medições ou desative os caches listados abaixo. Não compare execuções com e sem cache.
• Registre uma duração representativa, como a mediana das execuções repetidas após as execuções de aquecimento, em vez de se basear no resultado mais rápido ou mais lento.
• Altere uma variável por vez para que seja possível associar uma diferença de desempenho a uma alteração específica.

Para uma comparação diagnóstica sem cache, desative o cache do sistema de arquivos do ClickHouse para dados remotos, o cache de consultas e o cache de condições de consulta. Desative também as projeções implícitas para que o count na execução C não use um plano de execução otimizado que ignore a varredura que você pretende comparar.

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

O fluxo de trabalho combina execuções controladas de consultas com medições do log de consultas:

Colete as medições de cada execução da seguinte forma:

1. Atribua um ID de consulta único a cada execução ou registre o ID gerado pela interface de consulta. Por exemplo, identifique execuções repetidas como bottleneck-a-1, bottleneck-a-2 e bottleneck-a-3. Com o clickhouse-client, passe --query_id your-query-id ao executar uma consulta.

2. Execute cada consulta de comparação várias vezes nas mesmas condições. Mantenha as execuções de aquecimento separadas das execuções medidas.

3. Execute o flush do log de consultas antes de procurar consultas concluídas recentemente:

   SYSTEM FLUSH LOGS;

   Se não puder executar SYSTEM FLUSH LOGS, aguarde o flush automático do log de consultas e tente a busca novamente. Se o registro nunca aparecer, verifique se o log de consultas está habilitado, se você tem permissão para ler system.query_log e se está consultando o nó que executou a consulta.

4. Procure o registro concluído de cada ID de consulta. system.query_log registra os eventos QueryStart e QueryFinish de uma consulta concluída. Filtre por QueryFinish, que contém a duração final, as linhas e os bytes lidos, além do pico de memória:

   SELECT
       query_id,
       query_duration_ms,
       read_rows,
       read_bytes,
       memory_usage
   FROM system.query_log
   WHERE type = 'QueryFinish'
     AND query_id = 'your-query-id'
   ORDER BY event_time_microseconds DESC
   LIMIT 1;

5. Para cada versão da consulta, use a duração mediana das execuções medidas. Registre read_rows, read_bytes e o pico de memória da execução mais próxima dessa mediana, para que as medições permaneçam vinculadas a uma execução real.

Use uma tabela como a seguir para organizar as medições representativas. Consulte system.query_log para obter mais informações sobre seus campos e configuração.

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

Para demonstrar as três comparações, o exemplo usa a carga de trabalho agrupada por intervalo de datas. Você pode aplicar o método a outra consulta sem seguir o exemplo passo a passo. Se a consulta não contiver GROUP BY, pule a execução B, conforme descrito abaixo.

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

Compare durações representativas de execuções repetidas, em vez de subtrair duas medições individuais. Diferenças grandes e consistentes indicam o que investigar a seguir:

Compare o read_rows da execução C com o valor retornado por seu count. Por exemplo, se read_rows for 100 milhões e count retornar 1 milhão, o ClickHouse examinou aproximadamente 100 linhas de origem para cada linha contada. Isso mostra que o filtro rejeitou a maioria das linhas lidas da tabela, mas não identifica o motivo. Essa razão se aplica a varreduras simples de uma única tabela. Para consultas com várias fontes de dados ou projeções, interprete read_rows usando o plano de execução.

No ClickHouse 25.9 e versões posteriores, desabilite o cache de condições da consulta e a aplicação dinâmica de data skipping indexes antes de inspecionar o uso dos índices:

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

Em seguida, use EXPLAIN indexes = 1 para verificar quais índices o ClickHouse usou e quantas partes e grânulos cada índice eliminou. Se o ClickHouse selecionou mais grânulos do que o esperado, verifique se os filtros estão alinhados à chave de ordenação da tabela e se a eliminação de partições ou um data skipping index poderia eliminar mais grânulos. Se o plano não tiver uma seção Indexes, o EXPLAIN não informou a poda por índice para essa consulta. Em contraste, espera-se que uma consulta analítica na tabela inteira leia a maior parte da tabela.

Depois que a comparação indicar um provável gargalo, valide-o antes de alterar o esquema ou a consulta. Use evidências adequadas à fonte suspeita de latência:

• Para um gargalo de varredura ou filtragem, use EXPLAIN indexes = 1 com as configurações descritas acima para ver quais índices o ClickHouse usa e quantas partes e grânulos cada índice elimina. Verifique se o plano usa uma projeção implícita em vez da varredura esperada.
• Para um gargalo de agrupamento ou agregação, inspecione os eventos de perfil de consulta relevantes e o pico de uso de memória.
• Se a execução C continuar lenta e contiver junções, compare-a a uma consulta de diagnóstico que remova uma junção por vez. Uma redução significativa na duração sugere que a junção removida contribui com uma carga de trabalho considerável. Como remover uma junção altera o significado da consulta, use essa comparação apenas para isolar o tempo de execução e interprete separadamente as alterações na contagem de linhas.
• Para um gargalo em outra operação mantida na execução C, inspecione o plano de execução e os eventos de perfil de consulta relevantes.

Consulte o guia de diagnóstico de consultas lentas para obter detalhes sobre as informações de índice retornadas por EXPLAIN. Aplique uma alteração pontual e repita as execuções A, B e C nas mesmas condições. Confirme que a alteração reduziu o trabalho pretendido e não deslocou o gargalo para outro ponto.

Prossiga para Abordagens de otimização para associar o possível gargalo a uma ou mais alterações específicas.