Оптимизации подзапросов в InterBase
Страница 4. Оптимизация скалярного подзапроса

Оптимизация скалярного подзапроса

Точнее, доказательство её отсутствия.

select x from test1
where id = (select id from test2 where y = 130);

PLAN (TEST2 NATURAL)
PLAN (TEST1 INDEX (RDB$PRIMARY1))

По своей привычке InterBase выдаёт планы подзапросов первыми, до плана основного запроса.

Как можно видеть, условие в подзапросе вида id = id_извне никак на него не повлияло - он обрабатывается полным перебором. Попытка явно подсунуть ему план с индексом по test2(id) к успеху не приводит - возвращается ошибка. Зато внешний запрос индекс использует.

Теперь попробуем написать в точности то же самое, но через IN.

Аналогичный запрос, но через IN()

select x from test1
where id in (select id from test2 where y=130);

PLAN (TEST2 INDEX (RDB$PRIMARY2))
PLAN (TEST1 NATURAL)

Может показаться смешным, но замена = на IN перевернула весь план буквально с точностью до наоборот. Теперь внешний запрос начинает отрабатывать своё условие перебором, зато внутренний начинает чувствовать контекст. Условие из контекста аккуратно подходит под его индекс, что и используется.

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

Надо сказать, что оба запроса на самом деле дают результат, эквивалентный следующему соединению:

select test1.x from test1, test2
where test1.id=test2.id and test2.y=130;

Вариант со скалярным подзапросом даёт план, эквивалентный следующему:

PLAN JOIN (TEST2 NATURAL,TEST1 INDEX (RDB$PRIMARY1))

А вариант с множественным действует примерно так:

PLAN JOIN (TEST1 NATURAL,TEST2 INDEX (RDB$PRIMARY2))

В данном случае первый вариант эффективнее. Он делает один проход по test2, находит в ней всего одну запись, у которой y = 130, и с полученным значением выполняет внешний запрос. Вариант с соединением, однако, является более общим, так как скалярный подзапрос приведёт к ошибке, если записей с y = 130 окажется несколько.

Второй вариант, с IN это как раз стерпит, однако он менее эффективен, так как вызывает поиск по table2 на каждой итерации внешнего запроса. Правда, сам этот поиск делается по индексу.

И здесь ещё один существенный момент: при отработке подзапросов типа IN(...), =SOME(...), =ANY(...) перебор останавливается после первой же записи, выданной подзапросом. В то время как =ALL(...) будет работать либо до конца, либо до первой записи, не удовлетворяющей условию. То есть при удачном стечении обстоятельств, если ``подходящая'' запись всплывёт на первой же итерации подзапроса, всё может быть очень эффективно. А возможна и обратная ситуация.

Естественно, те же соображения применимы и при других видах сравнения. Операции <, <=, <> так же можно внести во внутренний запрос. Хотя пользы от этого, конечно, будет гораздо меньше, чем от равенства.

Кстати, в двух описанных примерах можно вместо y = 130 в подзапросе сделать x = 30 во внешнем запросе. На планы это не повлияет, поскольку и в том, и в другом случае условия налагаются на неиндексируемые поля. Однако оценки эффективности поменяются местами, и вариант с подзапросом через IN станет более эффективным. В прочем, ни один из вариантов с подзапросами никогда не будет эффективнее, чем оптимальный план в варианте с соединением. Потому невозможность автоматической раскрутки подзапросов в соединения является важным недостатком, который следует учитывать.