Почему оптимизатор генерит самый неоптимальный план запроса?

[Barvetal]

Есть проблема, оптимизатор строит на мой простейший запрос явно неоптимальный план. Помогите разобраться, как сделать чтобы Файерберд выполнял запрос так, как будет оптимально. Ниже привожу описание проблемы:

Есть запрос:

Код: Выделить всё

Select L.RECID
  From L
  Where L.VALID IN (
    Select ID
      From C
      Where C.FValue = 'test'
    )

Так вот, запускаю в IBExpert 2004.10.30 этот скрипт. План по этому скрипту строится следующий:

Код: Выделить всё

PLAN (L NATURAL)
PLAN (C_FLD_WORDS INDEX (RDB$PRIMARY75))

Информация о заполеннии таблиц:
L:
Количество записей: 2 846 058
Статистика по внешенему ключу на поле ValID: 0.00000785
C:
Количество записей: 127 238
Статистика по первичному ключу на поле ID: 0.00000785
Статистика по индексу на поле FValue: 0.00000785

Таким образом, что мы видим:
Идет натуральный перебор по таблице L. И для каждого значения L происходит индексный поиск по результату выполнения подзапроса.
Подзапрос возвращает одно значение. Таким образом, сервер выполняет перебор по почти 3 миллионам значений и для каждого из них делает поиск по индексированному набору данных как результат выполнения подзапроса (этот набор содержит в данном случае одну запись).

Отсюда очевидно, что было бы намного эффективнее выполнять данный сложный запрос в обратном порядке. То-есть, вначале выполнить внутренний подзапрос, и делать натуральный перебор по результату этого подзапроса, и для каждого значения этого результата выполнять индексный поиск по таблице L. Таким образом, был бы натуральный перебор по результату выполнения подзапроса (который содержит одну запись в данному случае), и для каждой этой записи производился бы индексный поиск по таблице L. И в этом случае мы бы имели вместо чтения 7 миллионов записей чтение около 120 тысяч записей.

Оптимизатор принимает дивное решение о натуральном поиске по L еще и потому, что таблица L содержит почти 3 миллиона записей, а таблица C - около 120 тысяч, и поэтому натуральный перебор по C был бы более логичный.

Отсюда собственно вопрос. Почему собственно оптимизатор работает таким вот образом, и, что более важно, как заставить его делать натуральный перебор по подзапросу, и для каждой записи подзапроса выполнять индексный поиск по таблице L? Помогите пожалуйста, очень нужно.

Данные о среде выполнения:
Версия сервера: Firebird 1.5.1
Версия ОС: MS Windows 2000 server

Скрипт создания таблиц:

Код: Выделить всё

CREATE DOMAIN TYPE_ID AS 
INTEGER
NOT NULL 

-- C Table --
CREATE TABLE C_FLD_WORDS (
    ID      TYPE_ID /* TYPE_ID = INTEGER NOT NULL */,
    FVALUE  VARCHAR(30)
);

ALTER TABLE C_FLD_WORDS ADD PRIMARY KEY (ID); -- RDB$PRIMARY75
CREATE INDEX C_FLD_WORDS_VAL ON C_FLD_WORDS (FVALUE);

-- L Table --
CREATE TABLE L_FLD_WORDS (
    RECID  TYPE_ID /* TYPE_ID = INTEGER NOT NULL */,
    VALID  TYPE_ID /* TYPE_ID = INTEGER NOT NULL */
);

ALTER TABLE L_FLD_WORDS ADD FOREIGN KEY (VALID) REFERENCES C_FLD_WORDS (ID); -- Индекс RDB$FOREIGN228

Всем заранее огромное спасибо за помощь!!

[kdv]

"почему я пишу неоптимальные запросы, а сервер на них тормозит"?
сервер выполняет абсолютно то, что ты хочешь. и строит при этом самый оптимальный план.

Код: Выделить всё

Select L.RECID From L
  Where L.VALID IN (
    Select ID From C
      Where C.FValue = 'test')

это то же самое что и

Select L.RECID
From L
LEFT JOIN C
ON C.ID = L.VALID
AND C.FValue = 'test'

по другому такой запрос выполнить нельзя. Т.е. надо таблицу L перебрать ВСЮ, и для каждой записи L выполнить поиск из C.

и еще - "натуральных переборов" по "результатам" выборки не бывает. Сейчас придет лесник и всех нафиг разгонит

[dimitr]

За кросс-постинг - выговор. Ответ см. на sql.ru.

[dimitr]

kdv тоже выговор Левый джойн тут совсем не причем. IN превращается во внутренний джойн по простому правилу:

Код: Выделить всё

Select L.RECID
From L Join ( Select Distinct ID From C Where C.FValue = 'test' ) TMP
On L.VALID = TMP.ID

Т.к. DISTINCT не даст возможности использовать индекс в джойне и в результате получится либо HASH JOIN, либо MERGE JOIN (причем обе с сортировкой поверху), то обычно применяется следующее правило: если результирующий набор подзапроса можно однозначно идентифицировать как уникальный на этапе prepare, то убирается DISTINCT и подзапрос соединяется с основным через LOOP JOIN или HASH JOIN. Если такой вывод сделать нельзя, то выполняется либо SEMI-JOIN, либо псевдо-EXISTS (оба без DISTINCT).

IB/FB выполняет только последнее преобразование.

[Barvetal]

это то же самое что и

Код: Выделить всё

Select L.RECID 
From L 
LEFT JOIN C 
ON C.ID = L.VALID 
AND C.FValue = 'test' 

Действительно...

Переписал написанный вами запрос в виде
Select L.RECID
From L JOIN C ON C.ID = L.VALID AND C.FValue = 'test'
То-есть, заменил LEFT JOIN на просто JOIN

Получил такой план:
PLAN JOIN (C INDEX (C_FLD_WORDS_VAL),L INDEX (RDB$FOREIGN228))

И как раз то, что я хотел, одно индексированное чтение из C таблицы, и 6 552 индексированных чтения из L таблицы! Работает намного быстрее! Доли секунды вместо почти двух минут. Спасибо!!!

Теперь такой вопрос. А почему мой запрос, описанный в топике, аналогичный запросу с LEFT JOIN? Зачем нужен именно LEFT джойн? Почему оптимизатор не вычисляет его как просто JOIN, без всяких LEFT? Это б работало намного эффективнее. Обьясните пожалуйста, потомучто не понимаю...

[kdv]

насчет полной эквивалентности я погорячился. Скорее, скажем, по плану запрос с in почти эквивалентен LEFT JOIN. Потому что идеальных оптимизаторов не существует. И в Оракле и в MS SQL оптимизатор в других местах, например, чудит еще как.

Оптимизатор принимает дивное решение о натуральном поиске по L еще и потому, что таблица L содержит почти 3 миллиона записей, а таблица C - около 120 тысяч, и поэтому натуральный перебор по C был бы более логичный.

"дивное" решение оптимизатор принял потому, что он не умеет делать из гамбургера корову, а из запроса с подзапросом - join. Ты в следующий раз пиши запрос так, как надо, а не наобум. подселекты и where field in subselect - вообще любимое дело начинающих. К сожалению, даже у Грабера join-ы не так хорошо описаны, как все остальное.