(написанное ниже прошу расценивать как информацию к размышлению).
Обнаружено странное поведение оптимизатора, когда он выбирает
явно неоптимальный план запроса. Описаны условия возникновения, солюшены.
Версии IDS, на которых тестировалось: 7.31.UD8, 9.40.UC8, 11.50.FC1TL .
Параметры конфига IDS, касающиеся оптимизатора:
1.
2.
3.
4.
5.
6.
OPTCOMPIND 2 # Use costs regardless of the transaction isolation
OPT_GOAL 0 # FIRST_ROWS
DIRECTIVES 1 # Optimizer DIRECTIVES ON ( 1 /Default) or OFF ( 0 )
DS_MAX_QUERIES 3 # Maximum number of decision support queries
DS_TOTAL_MEMORY 524288 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
Таблица, на которой проявился эффект:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE "informix".sd_lines(
id_line INTEGER NOT NULL,
...
id_doc INTEGER NOT NULL,
...
) EXTENT SIZE 35000 NEXT SIZE 131072 LOCK MODE ROW;
...
CREATE UNIQUE INDEX "informix".p287_664 ON "informix".sd_lines(id_line);
CREATE INDEX "informix".r287_1961 ON "informix".sd_lines(id_doc);
...
Количество строк в таблице:
1.
2.
3.
4.
SELECT count(*)
FROM sd_lines;
7435707
Из них (как выяснилось), эффект проявляется примерно на 700 строках (+/-).
Простой запрос, на котором "спотыкается" оптимизатор (далее -
"глючный" ):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
QUERY: (FIRST_ROWS OPTIMIZATION)
------
SELECT {+EXPLAIN, AVOID_EXECUTE}
id_line
FROM sd_lines
WHERE id_doc = 13406526
ORDER BY id_line
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 939558
Estimated # of Rows Returned: 2651
1 ) informix.sd_lines: INDEX PATH
Filters: informix.sd_lines.id_doc = 13406526
( 1 ) Index Keys: id_line (Serial, fragments: ALL)
Тот же запрос, но з другим значением в фильтре, на нем оптимизатор
ведет себя предсказуемо:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
QUERY: (FIRST_ROWS OPTIMIZATION)
------
SELECT {+EXPLAIN, AVOID_EXECUTE}
id_line
FROM sd_lines
WHERE id_doc = 13437160
ORDER BY id_line
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 10
Estimated # of Rows Returned: 25
Temporary Files Required For: Order By
1 ) informix.sd_lines: INDEX PATH
( 1 ) Index Keys: id_doc (Serial, fragments: ALL)
Lower Index Filter: informix.sd_lines.id_doc = 13437160
1.
2.
3.
"Видимые" признаки "глюка":
---------------------------
Из вышеуказанных запросов видим, что они отличаются
1) значением в фильтре WHERE
2) Estimated Cost: 939558 против 10
3) используемым оптимизатором индексом:
"Index Keys: id_line"
против
"Index Keys: id_doc"
4) (как следствие 2) ) - временем выполнения: ~1мин. против долей
секунды
1.
2.
3.
"Неочевидные" причины "глюка":
------------------------------
Как показало исследование вопроса, "глюк" проявляется на значениях фильтра,
которые, согласно имеющемуся распределению данных в таблице, имеют низкую
избирательность фильтра. А именно, если посмотреть в "data distibution"
по этому столбцу таблицы:
1.
2.
3.
4.
$ dbschema -hd sd_lines -d database > sd_lines_distribution.txt
Distribution for informix.sd_lines.id_doc
High Mode, 0 . 500000 Resolution
... то увидим (проверено экспериментально), что "глюк" проявляется
только на bin's, в которых значение второго поля (кол-во уникальных
значений в bin) меньше некоторого числа (в данном случае < 36):
значение из первого запроса (13406526) попадает в 170й bin:
1.
2.
3.
169 : ( 37113 , 72 , 13394330 )
170 : ( 37113 , 14 , 13427487 )
171 : ( 37113 , 1492 , 13488741 )
значение из второго запроса (13437160) попадает в 171й bin.
А также на значениях из "data distribution", попавших в bin's
из раздела OVERFLOW. (в терминах "обычных" bin-ов там число во
втором поле вообще было бы < 1 ).
Таким образом, получается, что оптимизатор выбирает другой индекс
при условии низкой селективности фильтра для конкретного его значения.
НО! Что удивительно для меня: получившееся значение "Estimated Cost",
превышающее такое же для случая использования другого индекса
в ~1000 - 100 000 раз (!) его НЕ СМУЩАЮТ :)
1.
2.
3.
Что ПОМОГАЕТ для "вразумления" оптимизатора (workaroud/solution):
------------------------------------------------------------------
а) изменение переменной конфига OPT_GOAL :
б) использование директивы оптимизатора INDEX для принудительного
выбора "правильного" индекса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
QUERY: (FIRST_ROWS OPTIMIZATION)
------
SELECT {+EXPLAIN, AVOID_EXECUTE, INDEX (sd_lines r287_1961)}
id_line
FROM sd_lines
WHERE id_doc = 13406526
ORDER BY id_line
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
INDEX ( sd_lines r287_1961 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 1276
Estimated # of Rows Returned: 2651
Temporary Files Required For: Order By
1 ) informix.sd_lines: INDEX PATH
( 1 ) Index Keys: id_doc (Serial, fragments: ALL)
Lower Index Filter: informix.sd_lines.id_doc = 13406526
1.
2.
3.
Что НЕ ПОМОГАЕТ:
----------------
а) Снижение RESOLUTION до 0.1 при UPDATE STATISTICS.
В этом случае распределение по bin'ам получается более равномерное,
но кол-во bin'ов в разделе OVERFLOW вырастает (до ~700) и на них
"глюк" все равно имеет место быть.
б) Изменение переменной конфига OPTCOMPIND
в) Использование директивы оптимизатора ALL_ROWS (что тоже удивительно,
исходя из положительного эффекта от "OPT_GOAL -1"):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
QUERY:
------
SELECT {+EXPLAIN, AVOID_EXECUTE, ALL_ROWS}
id_line
FROM sd_lines
WHERE id_doc = 13406526
ORDER BY id_line
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
ALL_ROWS
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 939558
Estimated # of Rows Returned: 2651
1 ) informix.sd_lines: INDEX PATH
Filters: informix.sd_lines.id_doc = 13406526
( 1 ) Index Keys: id_line (Serial, fragments: ALL)
Да, возможно, оптимизатор по заданному в конфиге критерию
OPT_GOAL и "имеет право на ошибку" (неоптимальный выбор плана
запроса). И в каждом конкретном случае дело программиста -
тюнинговать ли конкретный запрос директивами... Но, пардон,
не до такой же степени "промахиваться": итоговая разница
между Estimated Cost запроса с "автооптимизацией" и "без"
(939558 против 1276) просто ужасает... :)
А главное: никогда нельзя быть уверенным, не "тормознет" ли
на каком-то конкретном значении (комбинации значений) фильтра
тот или иной запрос... Всего же не протестируешь...
Думаю, это один из примеров, из-за которых в частности наши
программисты недолюбливают Informix...
PS. Сразу же отвечу на логичную реплику "так поставьте OPT_GOAL=-1
и будет вам щастье!": нет, из пушки по воробьям не стреляют. По мне
так логичнее сделать, чтобы действительно работала директива
оптимизатора {+ALL_ROWS}. А еще лучше, чтоб он был более интеллектуальным
и не допускал слишком большой разницы между Estimated Cost при подборе вариантов
плана запроса. Иными словами, при некоторых условиях выгоднее не следовать
тупо поведению, заданному переменной конфига OPT_GOAL.
