Низкая селективность по индексу / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Низкая селективность по индексу

6 сообщений из 6, страница 1 из 1

Низкая селективность по индексу

#39810473

sKot

Гость

Коллеги, доброго дня.
Есть таблица логов, в корой логируются данные в разрезе внешних систем (индексированный столбец int8):

Код: sql

1.
2.
3.
4.
5.
6.
7.

create table if not exists LOG_EXT_SYS (
   LOG_EXT_SYS_ID       ID                   not null,
   LOG_EXT_SYSTEM_ID    ID                   not null,
   LOG_BEGIN            INT8                 not null,
   LOG_REQUEST          VARCHAR(65535)       null,
   constraint PK_LOG_EXT_SYS primary key (LOG_EXT_SYS_ID)
);

По ней выполняется запрос:

Код: sql

1.
2.
3.
4.
5.
6.

select l__r.LOG_EXT_SYS_ID as __ID
 from LOG_EXT_SYS l__r
where
  l__r.LOG_EXT_SYSTEM_ID = <ID индексированного столбца>
 order by l__r.LOG_BEGIN asc
 offset 0 limit 50

Если делаем запрос по LOG_EXT_SYSTEM_ID = 6, то план получается такой:

Код: sql

1.
2.
3.
4.

Limit  (cost=0.56..128.58 rows=50 width=16) (actual time=0.438..4.836 rows=50 loops=1)
  ->  Index Scan using idx_log_begin on log_ext_sys l__r  (cost=0.56..1430998.17 rows=558897 width=16) (actual time=0.436..4.829 rows=50 loops=1)
        Filter: ((log_ext_system_id)::bigint = 6)
        Rows Removed by Filter: 5212

actual time=0.436

Если делаем запрос по LOG_EXT_SYSTEM_ID = 1, то план такой:

Код: sql

1.
2.
3.
4.

Limit  (cost=0.56..1019.20 rows=50 width=16) (actual time=21712.681..21717.558 rows=50 loops=1)
  ->  Index Scan using idx_log_begin on log_ext_sys l__r  (cost=0.56..1430998.17 rows=70241 width=16) (actual time=21712.679..21717.550 rows=50 loops=1)
        Filter: ((log_ext_system_id)::bigint = 1)
        Rows Removed by Filter: 32166998

actual time=21712.679

Запрос всегда пытается получить первые 50 записей. Планы идентичны, все столбцы индексированы.
На таблице включены статистики:

Код: sql

1.
2.

create statistics if not exists LOG_BEGIN  on LOG_BEGIN, LOG_EXT_SYS_ID from LOG_EXT_SYS;
create statistics if not exists LOG_BEGIN_EXT_SYSTEM_ID on LOG_BEGIN, LOG_EXT_SYSTEM_ID from LOG_EXT_SYS;

Вопрос:
Почему может возникать такой разброс "actual time", и как уменьшить "actual time" для "LOG_EXT_SYSTEM_ID = 1"?

...

Рейтинг:

0 / 0

07.05.2019, 01:46

| Ответить | Цитировать | Написать

Низкая селективность по индексу

#39810477

sKot

Гость

ps: кол-во записей в таблице: 53 482 921
Записей с LOG_EXT_SYSTEM_ID = 1 : 68 045
Записей с LOG_EXT_SYSTEM_ID = 6 : 567 154

...

Рейтинг:

0 / 0

07.05.2019, 02:11

| Ответить | Цитировать | Написать

Низкая селективность по индексу

#39810546

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

sKot

Код: sql

1.
2.
3.
4.
5.
6.

select l__r.LOG_EXT_SYS_ID as __ID
 from LOG_EXT_SYS l__r
where
  l__r.LOG_EXT_SYSTEM_ID = <ID индексированного столбца>
 order by l__r.LOG_BEGIN asc
 offset 0 limit 50

вам нужен составной индекс

Код: sql

 btree (LOG_EXT_SYSTEM_ID,LOG_BEGIN)

...

Рейтинг:

0 / 0

07.05.2019, 09:36

| Ответить | Цитировать | Написать

Низкая селективность по индексу

#39810563

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

sKot Вопрос:
Почему может возникать такой разброс "actual time", и как уменьшить "actual time" для "LOG_EXT_SYSTEM_ID = 1"?

Как уменьшить - уже совершенно верно подсказал коллега qwwq.
Теперь к вопросу почему такой разброс.
Вы планы привели, если их почитать то видно что база идет последовательно по индексу idx_log_begin перебирая все строки таблицы в порядке idx_log_begin от меньшего к большему

Код: plaintext

 Index Scan using idx_log_begin on log_ext_sys

После чего для каждой строки проверяет условие на LOG_EXT_SYSTEM_ID = <ID индексированного столбца>

Код: plaintext

 Filter: ((log_ext_system_id)::bigint = *)

И делает так пока не наберет требуемые вам offset 0 limit 50 строк

Код: plaintext

 Limit  ...

Разница только в одном:
для (log_ext_system_id)::bigint = 6 таким образом приходится перебирать

Код: plaintext

Rows Removed by Filter: 5212 + 50

строк
а для (log_ext_system_id)::bigint = 1 приходится перебирать аж

Код: plaintext

Rows Removed by Filter: 32166998 + 50

строк (т.е. почти всю таблицу)

Отсюда и разница в скорости.

Решение проблемы уже подсказали - составной индекс (два индекса по полям LOG_EXT_SYSTEM_ID и LOG_BEGIN - никак не заменяют в данном случае составной индекс по двум полям).

PS: у вас проблему усугубляет то что LOG_EXT_SYSTEM_ID = 1 в таблице расположен неравномерно и в начале таблицы (по полю LOG_BEGIN) таких строк нет. Если бы он был распределен равномерно то понадобилось бы выбирать не 32М строк а всего (кол-во записей в таблице: 53 482 921)/(Записей с LOG_EXT_SYSTEM_ID = 1: 68 045)*(limit 50) строк т.е. где то 30.000 строк или около того.
К сожалению на текущем этаме create statistics двумерную гистограмму не строит и не использует иначе бы это было отслежено (create statistics сейчас вычисляет только оценку уникальных значений в статистике и mcv (most common values) список, а это для вашего запроса бесполезно).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

07.05.2019, 10:11

| Ответить | Цитировать | Написать

Низкая селективность по индексу

#39811206

sKot

Гость

qwwq, Максим, спасибо за совет и за подробное разъяснение. Индекс помог :)
Отдельное спасибо Максиму за разъяснения по "Rows Removed by Filter" - я всегда считал, что это записи, отброшенные по индексному фильтру, а не перебранные поштучно.

...

Рейтинг:

0 / 0

08.05.2019, 12:05

| Ответить | Цитировать | Написать

Низкая селективность по индексу

#39811222

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Boguk,

а вот интересно, не сработал ли бы тут худо-бедно брин индекс по LOG_EXT_SYSTEM_ID ? (а оно умеет битмап брина на бтрии ?) . раз оно так почти скластеризовано для 1.

не в смысле побить составной -- тот без вариантов шустрее. а сэкономить на объёмах.

...

Рейтинг:

0 / 0

08.05.2019, 12:24

| Ответить | Цитировать | Написать

6 сообщений из 6, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Низкая селективность по индексу

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&gotonew=1&tid=1995210]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	53ms
get topic data:	11ms
get first new msg:	8ms
get forum data:	3ms
get page messages:	50ms
get tp. blocked users:	1ms
others:	14ms

total:	170ms