Crie sua primeira projeção

Sua tabela uk_price_paid está ordenada por (postcode, addr1, addr2). Isso significa que o ClickHouse pode pular grandes blocos de dados quando você filtra por postcode, addr1 ou addr2, mas consultas que filtram por town precisam varrer todas as linhas — 30 milhões ao todo.

Uma projeção armazena uma cópia adicional ordenada de (algumas ou todas as) colunas dentro da mesma tabela. Quando você consulta a tabela, o otimizador de consultas verifica automaticamente se ler da projeção exigiria acessar menos grânulos do que os dados base e, se for o caso, a utiliza de forma transparente.

Principais diferenças em relação a visões materializadas:

• Sem tabela separada - a projeção fica dentro da própria uk_price_paid
• Otimização transparente de consultas - você consulta uk_price_paid normalmente; o ClickHouse escolhe a projeção automaticamente
• Permanece sincronizada com mutações - exclusões e atualizações aplicadas à tabela são refletidas na projeção

Leia mais na documentação de referência sobre projeções.

Defina uma projeção em uk_price_paid que armazene town, date, price e type, ordenada por (town, date):

ALTER TABLE uk_price_paid
    ADD PROJECTION uk_price_paid_by_town
    (
        SELECT town, date, price, type
        ORDER BY (town, date)
    );

Isso registra a projeção nos metadados da tabela, mas não a materializa para os dados existentes - apenas as inserções futuras vão preenchê-la.

Verifique se a projeção aparece na definição da tabela:

SHOW CREATE TABLE uk_price_paid;

Você deverá ver o bloco PROJECTION uk_price_paid_by_town na saída.

Assim como as visões materializadas, uma projeção recém-adicionada só se aplica a inserções futuras. Para preenchê-la com os 30 milhões de linhas que já estão na tabela, materialize-a explicitamente:

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

Ela é executada como uma mutação em segundo plano. Você pode acompanhar seu progresso:

SELECT
    mutation_id,
    command,
    is_done
FROM system.mutations
WHERE table = 'uk_price_paid'
ORDER BY create_time DESC
LIMIT 5;

Quando is_done = 1, a projeção está totalmente materializada. Você também pode confirmar isso consultando system.projection_parts:

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

Agora execute uma consulta com filtro por town — na mesma tabela que você sempre consultou:

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

Verifique as estatísticas da consulta — muito menos linhas são lidas em comparação com antes de a projeção existir, porque o ClickHouse escolheu automaticamente ler da projeção uk_price_paid_by_town em vez de varrer os dados da tabela base.

Você pode confirmar que a projeção foi usada com EXPLAIN:

EXPLAIN
SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

Procure por ReadFromMergeTree na saída, fazendo referência ao nome da projeção. Se quiser comparar o desempenho explicitamente, você pode desativar a otimização de projeção para uma única consulta:

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC
SETTINGS optimize_use_projections = 0;

Isso força uma varredura completa da tabela para que você possa ver a diferença no número de linhas lidas.

Projeções e visões materializadas resolvem o mesmo problema — leituras mais rápidas em padrões de acesso alternativos —, mas envolvem trade-offs diferentes. Em resumo, as projeções são melhores quando você só precisa de uma ordenação diferente sobre os mesmos dados; visões materializadas são mais flexíveis quando você precisa transformar, agregar ou direcionar dados para um schema diferente. Para uma comparação detalhada, consulte Visões materializadas versus projeções.

As projeções armazenam uma segunda cópia das colunas selecionadas na mesma tabela, aumentando o uso de espaço em disco. Consulte system.parts para ver o tamanho total de uk_price_paid (que agora inclui os dados da projeção):

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY table;

Você também pode verificar o armazenamento da projeção:

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS projection_size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

Este é o mesmo trade-off fundamental das visões materializadas: mais espaço em disco em troca de leituras mais rápidas. A projeção pode ser menor do que uma cópia completa porque inclui apenas as quatro colunas selecionadas, e a compressão varia conforme a ordem de ordenação.