принцип работы " ilike '%' "

Здравствуйте. Никак не могу понять почему такая разница в скорости выполнения хранимой процедуры. Хотя это в принципе одно и то же.

PostgreSQL 9.1.

В первом варианте выполняется ~50ms

CREATE OR REPLACE FUNCTION get_data(IN source_str varchar(100))
RETURNS TABLE(tag1 varchar(100), tag2 varchar(100), tag3 varchar(100)) AS
$BODY$
BEGIN
RETURN QUERY
select table1.name, table2.name, table3.name
from table1, table2, table3
where table3.id = table2.id_tab3
and table1.id_tab2 = table2.id
and (table1.name ilike 'xyz%')
and (table2.name ilike '%')
and (table3.name ilike '%')
LIMIT 1000;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

В этом варианте выполняется 2500 ms

CREATE OR REPLACE FUNCTION get_data(IN source_str varchar(100))
RETURNS TABLE(tag1 varchar(100), tag2 varchar(100), tag3 varchar(100)) AS
$BODY$
declare str1 varchar(100);
declare str2 varchar(100);
declare str3 varchar(100);
BEGIN
str1='xyz%';
str2='%';
str3='%';
RETURN QUERY
select table1.name, table2.name, table3.name
from table1, table2, table3
where table3.id = table2.id_tab3
and table1.id_tab2 = table2.id
and (table1.name ilike str1)
and (table2.name ilike str2)
and (table3.name ilike str3)
LIMIT 1000;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

Опции просмотра комментариев

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

На мой взгляд (не претендую

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

Вряд ли. Попробовал

Вряд ли.
Попробовал вариант:

CREATE OR REPLACE FUNCTION get_data(IN source_str varchar(100))
RETURNS TABLE(tag1 varchar(100), tag2 varchar(100), tag3 varchar(100)) AS
$BODY$
declare str1 varchar(100);
declare str2 varchar(100);
declare int3 integer;
BEGIN
str1='xyz%';
str2='%';
int3=45;
RETURN QUERY
select table1.name, table2.name, table3.name
from table1, table2, table3
where table3.id = table2.id_tab3
and table1.id_tab2 = table2.id
and (table1.name ilike str1)
and (table2.name ilike str2)
and (table3.id in (1, int3))
LIMIT 1000;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

И время выполнения стало ~ 20ms. Показанная часть хранимой процедуры - это только часть большей процедуры, где также используются входные параметры, но замедляет процесс выполнения именно та часть, что я показал.
Мне кажется, что тут какая то непонятная мне специфика поиска в полях строкового типа по шаблону.
Я просмотрел документацию и не нашел причин, которые могли бы так замедлять поиск., поэтому решил обратиться к профессионалам. Может кто сталкивался с такой ситуацией.

Возможно, разница в скорости из-за кэша

Если выборка "ездит" по одним и тем же записям (страницам), то первый проход всегда выполняется дольше (иногда в неск.раз), чем последующие (в зависимости от размеров выделенной под кэш и шаред памяти). Так что, иногда дело может быть не в принципах работы оптимизатора, а в порядке выполнения примеров в сочетании с настройками сервера и sql-сервера. Поэтому, толкайте пример минимум дважды - для нивелирования этих расхождений.
А касательно работы (i)like - примерный алгоритм оптимизатора для разных типов индекса в сочетании с шаблоном есть в описании. Вроде бы, там индекс подключается, для шаблонов вида 'начало строки%'. В остальных случаях используется перебор записей.

Опции просмотра комментариев

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

Back to top

(С) Виктор Вислобоков, 2008-2023