да, нужна эвристика (т.е. структура "пути" позволяет некоторые вольности)...
2 подзадачи.
1. для каждой строки в 100 млн таблице сделать проверку на то является ли она одной подстрокой одной из строк 100 тыс таблицы  (это вообщем то решилось массивом родителей и развертыванием оного + дистинктом)

2. для каждой строки в 100 млн таблице проверить является ли одна из строк в 100 тыс таблице её подстрокой (тут уже сложнее, так как каждую строку в 100 млн таблице не поразбиваешь)... можно ли как то сделать словарь всех объектов? и вытащить из него во временную таблицу и уже применить прямой join/in? dictGetHierarchy наоборот?

я вижу некоторую разницу между
ORDER BY (path)
ORDER BY (lvl1_val,lvl2_val,lvl3_val)

тебе вернуть нужно какие данные? просто пути?

можно и просто пути, но в конечном итоге есть ещё больше факт таблица

path, key, value

и надо вернуть всю кучу key-value для фильтра, его родителей и детей

Я к тому. что может просто с помощью MAT VIEW сразу размножать запись сразу по всем ее родителям?

т.е. если пользователь просит

lvl1=2,lvl2=3,lvl3=5

надо вывести результаты для всех родителей и детей тоже

lvl1=2,lvl2=3,lvl3=5 -- filter object itself
lvl1=2,lvl2=3,lvl3=5,lvl4=2 -- child of filter object
lvl1=2,lvl2=3,lvl3=5,lvl4=44 -- child of filter object
lvl1=2,lvl2=3 -- parent of filter object

ну я про это

join не обязателен, но во второй таблице может быть до 100 тыщ элементов...

ага, пользователь в фильтре выбирает N путей, и в конечном счете может выбрать вплоть до 100 тыщ элементов?

да, UI строит деревья по нескольким "плоскостям"

просто допустим, я вижу как для одного пути кликхаус с легкостью достанет и путь и подпути и родителя по индексу.
Но проблема в том, что таких путей очень много?