Добрый день, CREATE TABLE xxx (

name String,
ts UInt4,
type Int8 DEFAULT -1,
value Int8 DEFAULT -1
)
ENGINE = ReplacingMergeTree(ts)
PRIMARY KEY(name)
ORDER BY (name,type)
SETTINGS index_granularity = 1024;

Делаю запрос

select name,min(value) from xxx group by name order by name limit 10;

почему CH ( 21.1.10.3 ) сканирует всю таблицу и отменяет запрос по памяти?

Ведь name - это primary key и все данные лежат в одной/соседних гранулах достаточно только прочитать первые несколько гранул и выдать результат

ну наверное потому что у вас name очень высоко кардинальный и чтобы сделать GROUP BY name надо в памяти сделать здоровущую hash table в которой будут в качестве ключей все ваши name и только потом применить к этому limit и еще есть ньюанс min(value) для ReplacingMergeTree не имеете особого смысла попробуйте SELECT name, value FROM xxx FINAL ORDER BY name LIMIT 10

это какая-то реальная задачи или синтетический пример для понимания работы ch? если name - это primary key зачем делать group by?

там же ORDER BY (name,type) в таблице ключ сортировки.

это помогает при сортировке ORDER BY в запросе... и при выборке WHERE при GROUP BY это ничего не ускоряет

ну можете включить optimize_aggregation_in_order

если вылетает по памяти, не факт что поможет

Я к тому, что ваш пример SELECT name, value FROM xxx FINAL ORDER BY name LIMIT 10 выдаст несколько результатов, если для одного name в таблице есть несколько type. А человеку, судя по его запросу, нужен один результат для каждого name, с минимальным value

>и чтобы сделать GROUP BY name надо в памяти сделать здоровущую hash table хм, я тогда наверное не правильно представлял себе как CH хранит данные, я думал, что в грануле лежат все записи принадлежащие primary key, те нужно только прочитать строки с одинаковым ключом и дальше их можно уже выдавать, так как в других гранулах этого ключа быть не может >и еще есть ньюанс min(value) для ReplacingMergeTree не имеете особого смысла почему? Обратите внимание, кроме Primary key там в таблице есть еще ORDER BY с еще одиним полем, Предположим у нас есть работники Иванов/Петров, и переиодически мы их измеряем в талии,бедрах и груди (. и мы хотим узнать минимальный размер работника. Вроде min как раз для этого и подходит select * from xxx order by name; ┌─name───┬─ts─┬─type─┬─value─┐ │ Ivanov │ 1 │ 10 │ 100 │ │ Ivanov │ 1 │ 20 │ 200 │ │ Ivanov │ 1 │ 30 │ 300 │ │ Petrov │ 1 │ 30 │ 600 │ │ Petrov │ 1 │ 10 │ 400 │ │ Petrov │ 1 │ 20 │ 500 │ └────────┴────┴──────┴───────┘ select name,min(value) from xxx group by name order by name limit 10; ┌─name───┬─min(value)─┐ │ Ivanov │ 100 │ │ Petrov │ 400 │ └────────┴────────────┘ И таких "работников" у меня 500 миллионов , как найти минимальное значение среди всех измерений? те каким образом построить таблицу,

>если name - это primary key зачем делать group by? Обратитие внимание, помимо primary в таблице есть еще ORDER BY с дополнительным полем, именно это поле и дает несколько разных строк на поле name

возможно я неправильно строю таблицу, опишу задачу может кто подскажит в какую сторону смотреть Есть 500 миллинов работников, мы мх измеряем в трех местах, раз в несколько дней. В один день например измерили всех в талии, в другой день всех измерили в бедрах. периодически нужно получать список всех работников с минимальным размером (не важно какого параметра) Две проблемы, которые пытаюсь решить 1) Как составить таблицу и запрос, чтобы они были как можно легче с точки зрения производительности 2) Как получить данные в клиента? клиент не может переварить 500 миллионов строк сразу. Мы используем подключение к CH как к Postgres серверу и изначально была идея делать запросы вида select ..... where name>'' order by name limit 100; select ..... where name>'<тут последнее значение из первого запроса>' order by name limit 100; так как полагал, что order by name (который primary key) - это дешевая операция, но видно, что это не так. По сути порядок записей не важен.

насчёт вопроса 2: насколько я понимаю, пагинация в clickhouse делается через max_block_size. Вот пример для питонячего драйвера: https://clickhouse-driver.readthedocs.io/en/latest/quickstart.html#streaming-results

да наверное я не до конца разобрался извините

если все что вам надо - получать сортированный список сотрудников по размеру талии, причем с пагинацией, причем сначала малые, то ваш order by (точнее primary key) и будет этим размером талии. Вот тогда выборка будет быстрой, и можно почти безнаказанно делать последовательные запросы с limit/offset.

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

если критерии выборки столь разнообразны, то от полной переборки таблицы вам никуда не уйти. Считаете свои миллиарды записей, и это будет тормозить на каждом запросе. В качестве альтернативы можете потратить дисковое пространство и сделать несколько MV с хранением, оптимизированным под каждый конкретный запрос. Т.е. сложные рассчеты/сортировки будут при вставках (и тормозить их), а не при выборке с пагинацией. Если вставка раз в день, то наверное пойдет. Но может у вас есть ещё нюансы, про которые вы пока не рассказали. Так-же можете посмотреть в сторону projections. Это экспериментальная фича, но суть именно та - оптимизация работы с подобной пачкой связанных MV.

>то от полной переборки таблицы вам никуда не уйти. да с этим и не спорю, к этому и готовы. Сами данные занимают мало места (там везде Uint32, UInt64 только(, прочитать их полность с nvme диска - вообще не проблема (время нескольких минут) вопрос в том можно ли как-то ускорить запрос вида select <тут аггрегации над групой строк> from xxx where name > 'A' group by name order by name limit 100; Если данные расположены в упорядоченном по primary key порядке, то по сути нужно прочитать 300-600 строк (если предположить, что каждая вторая запись фильтруется) и отдать в клиента. А сейчас это приводит к почти полной вычитке базы

с чего бы это 300 строк? Именно что для решения вашей задачи нужно прочитать 100% данных. И как вы сами пишете - это несколько минут. Вот столько и будет занимать каждый запрос. 300 строк - это 300 сотрудников. С чего вы взяли что в них будет искомый минимал? Почему не самый последний, с фамилией на на букву Я?

Я возможно не понимаю, но моя логика вот такая - name у нас primary key, значит на него в памяти есть индекс (с засечками) и данные лежат в таком порядке Алексеев - row 1 Алексеев - row 2 Алексеев - row 3 Петров - row 1 Петров - row 2 Яблоков - row 1 Яблоков - row 2 .. Есть запрос select name,count(*) from xxx group by name where name > 'A' limit 2; 1) По индексу определяем, что читать нужно с Петрова. 2) Прочитали две строчки петрова, выдали в клиента - Петров, 2 3) Прочитаели две строчки Яблокова - выдали в клиента Яблоков,2 4) Два результата у нас есть, ровно столько сколько и просили, завершаем запрос. Зачем читать другие строки?

так вы же писали что хотели получить минимальную талию? Просто количество - это очень просто.

я хочу получить минимальный размер измерения у каждого сотрудника. Сравнивать этого сотрудника с другими мне не нужно. Да даже если отвлечься от размеров. Вот запрос select name,count(*) from xxx where name > 'A' group by name limit 2; Уходит в фулл скан, Ставим опцию SET optimize_aggregation_in_order = 1; Все начинает работать быстро, но нам еще нужно как-то упорядочивать данные, чтобы в следующем запросе не получить те же самые данные, добавляем order by select name,count(*) from xxx where name > 'A' group by name order by name limit 2; и снова уходим в фулл скан

https://github.com/ClickHouse/ClickHouse/issues/21756 Ну ставьте лайки и тд тогда

Зачем вам по две строки на сотрудника если нужен минимальный размер?

Не совсем понял Вашего вопроса. У каждого сотрудника несколько строк, каждая строка это результат измерения разных данных проведенные в разние дни. В понедельник всем измерили талию, во вторник всем измерили грудь. В конце недели нужно получить цифру, какой минимальный размер у каждого сотрудника

Order by не добавляет. Но ch не имеет constraint-ов, поэтому дубликаты могут быть. Поэтому да, груп бай там может быть, поэтому согласен, мой вопрос был лишним.

>select name,count(*) from xxx where name > 'A' group by name order by name limit 2; Ну уходите не в фулл скан, а в фулл сортировку, это другое Во вторых если вы используете optimize_aggregation_in_order ORDER BY name скорее всего не нужен

> а в фулл сортировку а сортировку чего? результатов или исходных строк? >Во вторых если вы используете optimize_aggregation_in_order ORDER BY name скорее всего не нужен вот это бы решило мою проблему, насколько на это безопасно закладываться? Это так задуманно или то, чтоORDER BY не нужен, это просто особенность реализации, которую завтра могут изменить?

> вот это бы решило мою проблему, насколько на это безопасно закладываться? Это так задуманно или то, чтоORDER BY не нужен, это просто особенность реализации, которую завтра могут изменить? > насколько на это безопасно закладываться? Гарантий не дам, но думаю достаточно нормально. > Это так задуманно или то, чтоORDER BY не нужен, это просто особенность реализации, которую завтра могут изменить? Просто не сделана фича (я кидал ссылку на issue), что можно использовать комбинацию GROUP BY и ORDER BY in_order

ну тогда здорово, и еще один вопрос. В рамках одной версии клика - поведение всегда детерминировано? те если версию менять не планирую в следующие два года - то могу не беспокоится, что однажды этот запрос сработает неверно? (если конечно на баг какой-нить не напорюсь)

Да, ну разве что действительно баг. на самом деле для SELECT * FROM (SELECT * FROM table ORDER BY xx) Точно есть гарантии, что порядок сохраняется, так что не вижу причин, что бы он нарушался после GROUP BY in order

👍

конечно поможет, у нас у одного клиента расход памяти с optimize_aggregation_in_order уменьшается в 200 раз, запрос правда медленнее в 20 раз. optimize_aggregation_in_order обрабатывает в streaming , идя по первичному ключу

group by не использует первичный ключ (по умолчанию) потому что это медленнее, регулируется параметром который написали выше

спасибо, теперь понятно что было и что изменилось с включением настройки optimize_aggregation_in_order

По-моему, на это можно полагаться только если у нас один шард и один поток. А так у нас может первым вернуться результат не самого первого в общей сортировке блока.

> А так у нас может первым вернуться результат не самого первого в общей сортировке блока. обычный GROUP BY и ORDER BY (+ in_order) же делается с учетом шардов, так что с этой точки зрения никакой разницы

ну вот допустим у нас один шард. И 2 потока. Первый поток прочитал первый блок данных в порядке order by таблицы, второй поток второй блок данных. Так получилось, что второй поток оказался быстрее первого и первым вернул результат. Если в самом запросе нет order by, то разве у нас КХ не выдаст результаты, которые вернул второй поток, если их достаточно по limit? Не дожидаясь первого потока. Ибо зачем?

Пока первый поток не отдаст данные дальше второй поток допустим будет отдыхать или сложит данные в памяти куда то. (одна из причин, почему aggregation_in_order может быть медленнее) Ну я к тому, что ваша проблема никак не относится к GROUP BY ORDER BY in_order Если бы было так просто, то оно бы вообще ни для каких запросов не работало