EnПроцедура пересчета селективности индексов
(c) kdv, 13.04.2020
Оптимизатор в Firebird (и в InterBase) при анализе запроса и выборе плана выполнения запроса использует 2 основных критерия - размер таблиц (кардинальность) и избирательность индексов (селективность, selectivity).
Селективность индексов считается как 1 делить на количество уникальных значений в индексированном столбце (или столбцах). Например, если это индекс по первичному ключу, и в таблице 10 тысяч записей, то его селективность будет равна 1 / 10000 = 0,0001. А если в этой же таблице есть столбец с 10 разными значениями, то у индекса по этому столбцу селективность будет 1/ 10 = 0,1. Для оптимизатора чем меньше селективность индекса - тем лучше.
Если размер таблицы оптимизатор всегда определяет более-менее актуально, то селективность индексов не пересчитывается автоматически. В результате оптимизатор будет оценивать селективность, которая далека от реальности. Так что, селективность нужно пересчитывать самостоятельно.
Пересчет селективности индексов происходит при их создании, при alter index active, и при set statistics index indexname.
Например, вы создали таблицу, создали индексы, а затем наполнили таблицу данными. У всех индексов этой таблицы селективность так и будет равна 0, пока вы не пересчитаете селективность.
Или, вы создали таблицу, индексы, заполнили данными, сделали бэкап-рестор (при ресторе индексы создаются заново, и селективность будет актуальной). А потом в некоторых таблицах поменяли 30-40% данных (добавили, удалили, или изменили). И селективность тоже будет неактуальной.
Посмотреть хранимую селективность индексов можно запросом
(у неактивных индексов селективность будет равна -1)
На работающей базе нет смысла перестраивать индексы через alter index, это может быть долго. Гораздо быстрее пересчитать селективность командой set statistics index indexname.
Самым простым способом пересчитать селективность всех индексов БД - это выполнить запрос
Этот запрос, конечно, выдаст текст set statistics для всех индексов всех таблиц. Отсюда можно было бы исключить индексы по системным таблицам, добавив условие
Однажды, рассматривая содержимое дистрибутива InterBase 2020, я обнаружил интересный файл ris.sql, который поставляется в комплекте с версии XE3.
В ris.sql находится процедура для пересчета индексов, которая использует execute statement для set statistics, и позволяет выборочно обновлять статистику для разных типов индексов. Оказалось, что эта процедура прекрасно работает на Firebird, без изменений.
Вот текст этой процедуры:
(убрана часть комментария, аналогичная описанию выше, остальные комментарии перевел)
Собственно, для Firebird здесь можно было ничего не менять, всё работает. Я разве что закомментировал условие на RDB$ENCRYPTIONS, такой таблицы в Firebird нет. Но можно было и не комментировать, т.к. это условие всегда выдаст True.
Примеры вызова процедуры (из того же ris.sql):
Нужно отметить, что у InterBase последних версий появились индексы на таблицах TMP$ (аналог в Firebird - таблицы MON$), пересчитывать селективность которых не имеет смысла. Также, и в Firebird и в InterBase можно было бы исключать из пересчета индексы, построенные по GTT (Global Temporary Tables).
Впрочем, при желании эти дополнения вы можете сделать самостоятельно.
Вопросы? Пишите на support@ibase.ru.
Оптимизатор в Firebird (и в InterBase) при анализе запроса и выборе плана выполнения запроса использует 2 основных критерия - размер таблиц (кардинальность) и избирательность индексов (селективность, selectivity).
Селективность индексов считается как 1 делить на количество уникальных значений в индексированном столбце (или столбцах). Например, если это индекс по первичному ключу, и в таблице 10 тысяч записей, то его селективность будет равна 1 / 10000 = 0,0001. А если в этой же таблице есть столбец с 10 разными значениями, то у индекса по этому столбцу селективность будет 1/ 10 = 0,1. Для оптимизатора чем меньше селективность индекса - тем лучше.
Если размер таблицы оптимизатор всегда определяет более-менее актуально, то селективность индексов не пересчитывается автоматически. В результате оптимизатор будет оценивать селективность, которая далека от реальности. Так что, селективность нужно пересчитывать самостоятельно.
Пересчет селективности индексов происходит при их создании, при alter index active, и при set statistics index indexname.
Например, вы создали таблицу, создали индексы, а затем наполнили таблицу данными. У всех индексов этой таблицы селективность так и будет равна 0, пока вы не пересчитаете селективность.
Или, вы создали таблицу, индексы, заполнили данными, сделали бэкап-рестор (при ресторе индексы создаются заново, и селективность будет актуальной). А потом в некоторых таблицах поменяли 30-40% данных (добавили, удалили, или изменили). И селективность тоже будет неактуальной.
Посмотреть хранимую селективность индексов можно запросом
(у неактивных индексов селективность будет равна -1)
select i.rdb$relation_name, i.rdb$index_name, i.rdb$statistics
from rdb$indices i
-- исключить индексы по системным таблицам
-- where coalesce (rdb$system_flag, 0) = 0
order by 1, 2
from rdb$indices i
-- исключить индексы по системным таблицам
-- where coalesce (rdb$system_flag, 0) = 0
order by 1, 2
На работающей базе нет смысла перестраивать индексы через alter index, это может быть долго. Гораздо быстрее пересчитать селективность командой set statistics index indexname.
Самым простым способом пересчитать селективность всех индексов БД - это выполнить запрос
select 'set statistics index '||i.rdb$index_name||';'
from rdb$indices i
все полученные записи сохранить в текстовый файл, и выполнять его как скрипт при помощи ISQL.from rdb$indices i
Этот запрос, конечно, выдаст текст set statistics для всех индексов всех таблиц. Отсюда можно было бы исключить индексы по системным таблицам, добавив условие
where coalesce(i.rdb$system_flag, 0) = 0
(дело в том, что в системных таблицах 0 и null равнозначны по смыслу, поэтому приходится писать coalesce)Однажды, рассматривая содержимое дистрибутива InterBase 2020, я обнаружил интересный файл ris.sql, который поставляется в комплекте с версии XE3.
В ris.sql находится процедура для пересчета индексов, которая использует execute statement для set statistics, и позволяет выборочно обновлять статистику для разных типов индексов. Оказалось, что эта процедура прекрасно работает на Firebird, без изменений.
Вот текст этой процедуры:
(убрана часть комментария, аналогичная описанию выше, остальные комментарии перевел)
/*
* Copyright (C) 2011-2015 Embarcadero Technologies, Inc
* All Rights Reserved.
Процедура пересчитывает селективность индексов, и поддерживает следующие параметры:
- область действия процедуры (строковая константа):
'DATABASE' - все пользовательские индексы
'TABLE' - все индексы указанной таблицы
'INDEX' - только один индекс
'SYSTEM' - все индексы по системным таблицам
- имя объекта - имя таблицы или индекса, в точном соответствии регистра букв.
Если указана область действия 'DATABASE' или 'SYSTEM' то параметр должен быть NULL.
После вызова процедуры нужно сделать COMMIT.
*** Ограничения ***
- Для выполнения процедуры над всеми индексами БД вы должны быть
владельцем БД или SYSDBA
- скрипт будет работать только на тех версиях, которые поддерживают EXECUTE STATEMENT
*/
CREATE EXCEPTION CIS_INVALID 'Invalid argument to Stored Procedure COMPUTE_INDEX_SELECTIVITY';
CREATE PROCEDURE COMPUTE_INDEX_SELECTIVITY (
index_scope VARCHAR(20),
entity_name VARCHAR(68))
AS
DECLARE VARIABLE index_range INTEGER;
DECLARE VARIABLE table_name VARCHAR(68);
DECLARE VARIABLE index_name VARCHAR(68);
DECLARE VARIABLE ddl_string VARCHAR(150);
BEGIN
index_range = -1;
/* Check what index_range */
SELECT
CASE UPPER(:index_scope)
WHEN 'DATABASE' then 0
WHEN 'TABLE' then 1
WHEN 'INDEX' then 2
WHEN 'SYSTEM' then 3
ELSE -1
END
FROM RDB$DATABASE
INTO :index_range;
/* Обработка ошибочных параметров */
IF (index_range = -1) THEN BEGIN
EXCEPTION CIS_INVALID;
EXIT;
END
/* Пересчет селективности для всех индексов в БД */
IF (index_range = 0) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE COALESCE (RDB$SYSTEM_FLAG, 0) = 0
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
/* Пересчет селективности всех индексов таблицы */
IF (index_range = 1) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$RELATION_NAME = :entity_name
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
/* Пересчет селективности конкретного индекса */
IF (index_range = 2) THEN BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :entity_name || ' ';
EXECUTE STATEMENT ddl_string;
EXIT;
END
/* Пересчет селективности всех системных индексов БД,
за исключением индексов по RDB$ENCRYPTIONS. Закомментировано для Firebird, т.к. в нём такой таблицы нет
*/
IF (index_range = 3) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$SYSTEM_FLAG=1
-- AND RDB$RELATION_NAME NOT IN ('RDB$ENCRYPTIONS')
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
END ;
* Copyright (C) 2011-2015 Embarcadero Technologies, Inc
* All Rights Reserved.
Процедура пересчитывает селективность индексов, и поддерживает следующие параметры:
- область действия процедуры (строковая константа):
'DATABASE' - все пользовательские индексы
'TABLE' - все индексы указанной таблицы
'INDEX' - только один индекс
'SYSTEM' - все индексы по системным таблицам
- имя объекта - имя таблицы или индекса, в точном соответствии регистра букв.
Если указана область действия 'DATABASE' или 'SYSTEM' то параметр должен быть NULL.
После вызова процедуры нужно сделать COMMIT.
*** Ограничения ***
- Для выполнения процедуры над всеми индексами БД вы должны быть
владельцем БД или SYSDBA
- скрипт будет работать только на тех версиях, которые поддерживают EXECUTE STATEMENT
*/
CREATE EXCEPTION CIS_INVALID 'Invalid argument to Stored Procedure COMPUTE_INDEX_SELECTIVITY';
CREATE PROCEDURE COMPUTE_INDEX_SELECTIVITY (
index_scope VARCHAR(20),
entity_name VARCHAR(68))
AS
DECLARE VARIABLE index_range INTEGER;
DECLARE VARIABLE table_name VARCHAR(68);
DECLARE VARIABLE index_name VARCHAR(68);
DECLARE VARIABLE ddl_string VARCHAR(150);
BEGIN
index_range = -1;
/* Check what index_range */
SELECT
CASE UPPER(:index_scope)
WHEN 'DATABASE' then 0
WHEN 'TABLE' then 1
WHEN 'INDEX' then 2
WHEN 'SYSTEM' then 3
ELSE -1
END
FROM RDB$DATABASE
INTO :index_range;
/* Обработка ошибочных параметров */
IF (index_range = -1) THEN BEGIN
EXCEPTION CIS_INVALID;
EXIT;
END
/* Пересчет селективности для всех индексов в БД */
IF (index_range = 0) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE COALESCE (RDB$SYSTEM_FLAG, 0) = 0
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
/* Пересчет селективности всех индексов таблицы */
IF (index_range = 1) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$RELATION_NAME = :entity_name
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
/* Пересчет селективности конкретного индекса */
IF (index_range = 2) THEN BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :entity_name || ' ';
EXECUTE STATEMENT ddl_string;
EXIT;
END
/* Пересчет селективности всех системных индексов БД,
за исключением индексов по RDB$ENCRYPTIONS. Закомментировано для Firebird, т.к. в нём такой таблицы нет
*/
IF (index_range = 3) THEN BEGIN
FOR SELECT RDB$RELATION_NAME, RDB$INDEX_NAME
FROM RDB$INDICES
WHERE RDB$SYSTEM_FLAG=1
-- AND RDB$RELATION_NAME NOT IN ('RDB$ENCRYPTIONS')
ORDER BY RDB$RELATION_NAME, RDB$INDEX_NAME
INTO :table_name, :index_name
DO
BEGIN
ddl_string = 'SET STATISTICS INDEX ' || :index_name;
EXECUTE STATEMENT ddl_string;
END
EXIT;
END
END ;
Собственно, для Firebird здесь можно было ничего не менять, всё работает. Я разве что закомментировал условие на RDB$ENCRYPTIONS, такой таблицы в Firebird нет. Но можно было и не комментировать, т.к. это условие всегда выдаст True.
Примеры вызова процедуры (из того же ris.sql):
-- пересчитать селективность всех индексов БД
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('DATABASE', NULL);
COMMIT;
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('DATABASE', NULL);
COMMIT;
-- пересчитать селективность индексов конкретной таблицы, например, после массовой вставки, удаления или изменения данных.
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('TABLE', 'EMPLOYEE');
COMMIT;
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('TABLE', 'EMPLOYEE');
COMMIT;
-- пересчитать селективность конкретного индекса
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('INDEX', 'NAMEX');
COMMIT;
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('INDEX', 'NAMEX');
COMMIT;
-- пересчитать селективность всех индексов по системным таблицам
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('SYSTEM', NULL);
COMMIT;
EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('SYSTEM', NULL);
COMMIT;
Нужно отметить, что у InterBase последних версий появились индексы на таблицах TMP$ (аналог в Firebird - таблицы MON$), пересчитывать селективность которых не имеет смысла. Также, и в Firebird и в InterBase можно было бы исключать из пересчета индексы, построенные по GTT (Global Temporary Tables).
Впрочем, при желании эти дополнения вы можете сделать самостоятельно.
Вопросы? Пишите на support@ibase.ru.