[ljoin] $lookup не задействует индекс при работе с интервалами

[PlatonB]

Вчера, по-сути, завершил проект high-perf-bio, реализовав в left_join отбор геномных интервалов одной MongoDB-коллекции по их вхождению/невхождению в интервалы из других коллекций БД. Но, как выяснилось, не без ложки дёгтя.

Пояснение для IT-специалистов, не специализирующихся на работе с генетическими данными. Что такое геномный интервал? Есть хромосомы - надмолекулярные сткуктуры, включающие, помимо всего прочего, ДНК. Есть нуклеотиды - мономеры ДНК. Сослаться на тот или иной участок ДНК можно так: обозначение хромосомы, номер стартового нуклеотида, номер конечного нуклеотида. Эти два нуклеотида - границы как раз геномного интервала. В биоинформатических таблицах чаще всего мы видим интервалы с выявленным влиянием на организм.

Aggregation pipeline из программы left_join, построенный по официальным докам MongoDB, выдаёт правильные результаты, но работает бесконечно долго для больших коллекций. Ни compound, ни одиночные индексы не ускоряют вычисление. Вот основа пайплайна - код левостороннего внешнего объединения коллекций - источника описываемой проблемы:

pipeline = [{'$lookup': {'from': right_coll_name,
                         'let': {'chrom': '$chrom', 'start': '$start', 'end': '$end'},
                         'pipeline': [{'$match': {'$expr': {'$and': [{'$eq': ['$$chrom', '$chrom']},
                                                                     {'$lt': [{'$max': ['$$start', '$start']},
                                                                              {'$min': ['$$end', '$end']}]}]}}}],
                         'as': right_coll_name.replace('.', '_')}} for right_coll_name in right_coll_names]

Эта же конструкция, но чуть упрощённая (Ilya Vorontsov):

pipeline = [{'$lookup': {'from': right_coll_name,
                         'let': {'chrom': '$chrom', 'start': '$start', 'end': '$end'},
                         'pipeline': [{'$match': {'$expr': {'$and': [{'$eq': ['$$chrom', '$chrom']},
                                                                     {'$lt': ['$$start', '$end']},
                                                                     {'$lt': ['$start', '$$end']}]}}}],
                         'as': right_coll_name.replace('.', '_')}} for right_coll_name in right_coll_names]

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

Кто-нибудь знает, как решить/обойти проблему игнора индексов?

P.S. Я уже создал аналогичную тему на официальном форуме MongoDB, но там далеко не всегда отвечают. Поэтому очень надеюсь на помощь коллег, знакомых и других посетителей репозитория.

@VorontsovIE, @yustinaivanova, может у вас будут какие-то идеи? Был бы очень благодарен.

Предполагаемые тормозящие факторы:

• Вложенный пайплайн, какое бы выражение в нём ни было. UPD: точно нет: если попробовать объединять аналогичной конструкцией не по интервалам, а по одиночным полям, то всё посчитается почти мгновенно.
• Подсчёт максимальной стартовой границы и минимальной конечной (Ilya Vorontsov).
• Сочетание работы по хромосомам с работой по интеравалам.
• Добавляемая к $lookup-пайплайну сортировка. Сразу говорю, что она не влияет.

[PlatonB]

Если кто мыслит на SQL, пишите на SQL, я не растеряюсь. Придумаете что-нибудь под любую привычную вам СУБД - будет не менее полезно.

[yustinaivanova]

Привет!
Странно что долго работает. Обычно SQL все быстрее выполняет.
Я к сожалению не специалист в MongoDB

[VorontsovIE]

Сорри, я не вполне разобрался в синтаксисе, но мне кажется, что у тебя операция min/max вынуждена пересчитывать границу для каждого объекта.
Если это так, то стоит убрать сложную логику из базы данных. Пусть она делает не полную операцию, но простую и быструю, а питон потом дофильтровывает оставшийся небольшой набор интервалов до итогового.

[PlatonB]

Илья, Юстина, спасибо!

Если применить конструкцию from-let-pipeline-as не к chrom-start-end, а к какому-нибудь одному полю (например, name для тех же BED), то команда выполнится моментально.

pipeline = [{'$lookup': {'from': right_coll_name,
                         'let': {'name': '$name'},
                         'pipeline': [{'$match': {'$expr': {'$eq': ['$$name', '$name']}}}],
                         'as': right_coll_name.replace('.', '_')}} for right_coll_name in right_coll_names]

Это говорит о том, что само наличие pipeline, вложенного в $lookup, не виновато. Значит, тормозит либо упомянутая Ильёй операция min/max, либо обработка сразу по хромосомам и позициям.

[PlatonB]

Избавился от $max и $min.

pipeline = [{'$lookup': {'from': right_coll_name,
                         'let': {'chrom': '$chrom', 'start': '$start', 'end': '$end'},
                         'pipeline': [{'$match': {'$expr': {'$and': [{'$eq': ['$$chrom', '$chrom']},
                                                                     {'$and': [{'$lt': ['$$start', '$end']},
                                                                               {'$lt': ['$start', '$$end']}]}]}}}],
                         'as': right_coll_name.replace('.', '_')}} for right_coll_name in right_coll_names]

Та же хрень.

[VorontsovIE]

Не знаю...
Во-первых, попробуй избавиться от вложенных and-ов (and может принимать больше двух условий):

pipeline = [
    {"$lookup":
        {
            "from": right_coll_name,
            "let": {"chrom": "$chrom", "start": "$start", "end": "$end"},
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$and": [
                                {"$eq": ["$$chrom", "$chrom"]},
                                {"$lt": ["$$start", "$end"]},
                                {"$lt": ["$start", "$$end"]}
                            ]
                        }
                    }
                }
            ],
            "as": right_coll_name.replace(".", "_")
        }
    }
    for right_coll_name in right_coll_names
]

Во-вторых, посмотри, работает ли отдельно условие на хромосому и отдельно на одну из позиций - так же как ты отдельно проверял условие на имя.
Возможно, у какой-то у колонок проблемы с индексом, но сейчас ты тестируешь сразу три колонки, а проверяешь, что проблем нет - на четвертой.

[PlatonB]

После упразднения вложенного $and ничего не изменилось.

Отдельные условия. Оказывается, всё зависит от оператора.

• Неравенство. [{'$match': {'$expr': {'$lt': ['$$start', '$start']}}}] - ничего не используется:
  

• Равенство. [{'$match': {'$expr': {'$eq': ['$$start', '$start']}}}] - многочисленные обращения к индексу start_1:
  

[VorontsovIE]

Очень интересно. Попробуй пару вещей:

• имеет смысл упростить код: вместо $$start подставить константу, чтобы избавиться от этой сложности
• попробовать поставить индекс первым операндом (заменив lt на gt). Вдруг монга не умеет обращать операторы сравнения почему-нибудь...
• попробуй вообще минимальный запрос без мишуры с джойнами протестировать на использование индекса, а потом постепенно усложняй. Так ты поймешь, на каком этапе всё ломается.

[PlatonB]

имеет смысл упростить код: вместо $$start подставить константу, чтобы избавиться от этой сложности

Проверил - не влияет.

попробовать поставить индекс первым операндом (заменив lt на gt). Вдруг монга не умеет обращать операторы сравнения почему-нибудь...

Тоже не работает.

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

Запросы без агрегации точно индексы используют. И сортировка тоже (там, правда, свои тараканы - #5).