Создайте первую проекцию

Ваша таблица uk_price_paid отсортирована по (postcode, addr1, addr2). Это значит, что ClickHouse может пропускать большие блоки данных, когда вы фильтруете по postcode, addr1 или addr2, но при фильтрации по town запросам приходится сканировать каждую строку — все 30 миллионов.

Проекция хранит дополнительную отсортированную копию (некоторых или всех) столбцов внутри той же таблицы. Когда вы обращаетесь к таблице с запросом, оптимизатор запросов автоматически проверяет, позволит ли чтение из проекции прочитать меньше гранул, чем чтение из основных данных, и при необходимости прозрачно использует её.

Ключевые отличия от materialized views:

• Нет отдельной таблицы — проекция находится прямо внутри uk_price_paid
• Прозрачная оптимизация запросов — вы обращаетесь к uk_price_paid как обычно; ClickHouse автоматически выбирает проекцию
• Синхронизируется с мутациями — удаления и обновления, применённые к таблице, отражаются в проекции

Подробнее см. в справочной документации по проекциям.

Определите проекцию для uk_price_paid, в которой town, date, price и type хранятся с сортировкой по (town, date):

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

Это регистрирует проекцию в метаданных таблицы, но не материализует её для существующих данных — только будущие вставки будут её заполнять.

Убедитесь, что проекция присутствует в определении таблицы:

SHOW CREATE TABLE uk_price_paid;

В выводе должен быть блок PROJECTION uk_price_paid_by_town.

Как и materialized view, недавно добавленная проекция применяется только к будущим вставкам. Чтобы заполнить её 30 миллионами строк, уже находящимися в таблице, нужно материализовать её явно:

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

Эта операция выполняется как фоновая мутация. Вы можете проверить её прогресс:

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

Когда is_done = 1, проекция полностью материализована. Также можно убедиться в этом, проверив 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;

Теперь выполните запрос с фильтром по town — к той же таблице, к которой вы обращались ранее:

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;

Проверьте статистику запроса — считывается значительно меньше строк, чем до появления проекции, поскольку ClickHouse автоматически выбрал чтение из проекции uk_price_paid_by_town вместо сканирования исходных данных.

Убедиться, что проекция действительно использовалась, можно с помощью 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;

Ищите в выводе ReadFromMergeTree, где упоминается имя проекции. Если вы хотите явно сравнить производительность, можно отключить оптимизацию проекций для одного запроса:

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;

Это принудительно запускает полное сканирование таблицы, чтобы вы могли увидеть разницу в количестве прочитанных строк.

И проекции, и materialized views решают одну и ту же задачу — ускоряют чтение при альтернативных шаблонах доступа, — но предполагают разные компромиссы. Коротко говоря, проекции лучше подходят, когда нужен лишь другой порядок сортировки тех же данных; materialized views более гибки, когда требуется преобразовывать, агрегировать или направлять данные в другую схему. Подробное сравнение см. в разделе Materialized views versus projections.

Проекции хранят вторую копию выбранных столбцов в той же таблице, увеличивая использование диска. Выполните запрос к system.parts, чтобы увидеть общий размер uk_price_paid (который теперь включает данные проекций):

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;

Вы также можете проверить хранилище для конкретных проекций:

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;

Это тот же базовый компромисс, что и у materialized view: больше места на диске в обмен на более быстрое чтение. Проекция может быть меньше полной копии, поскольку включает только четыре выбранных столбца, а степень сжатия зависит от порядка сортировки.