Общеизвестным является тезис о том, что от избыточного индексирования страдают только DML-операции, а SELECTы только получают разнообразные бенефиты.
Однако существуют определённые нюансы, которые могут разрушить данную стройную картину мира.
Я попробую продемонстрировать возможную проблему на тестовом примере (кстати, почти аналогичная проблема наблюдалась в реальной ПРОМ-системе).
Начнём с создания довольно широкой таблицы:
create table test_fastpath_main (id bigint, id_doc1 bigint, id_doc2 bigint,id_doc3 bigint,id_doc4 bigint,id_doc5 bigint, id_doc6 bigint, id_doc7 bigint,id_doc8 bigint,id_doc9 bigint,id_doc10 bigint, id_doc11 bigint, id_doc12 bigint,id_doc13 bigint,id_doc14 bigint,id_doc15 bigint, id_doc16 bigint, workload VARCHAR(512), primary key (id));
Далее наполним её тестовыми данными:
insert into test_fastpath_main (id, id_doc1, id_doc2, id_doc3, id_doc4, id_doc5, id_doc6, id_doc7, id_doc8, id_doc9, id_doc10, id_doc11, id_doc12, id_doc13, id_doc14, id_doc15, id_doc16, workload) select g, mod(g,11) as id_doc1, mod(g,12) as id_doc2, mod(g,13) as id_doc3, mod(g,14) as id_doc4, mod(g,15) as id_doc5, mod(g,16) as id_doc6, mod(g,17) as id_doc7, mod(g,18) as id_doc8, mod(g,19) as id_doc9, mod(g,110) as id_doc10, mod(g,111) as id_doc11, mod(g,112) as id_doc12, mod(g,113) as id_doc13, mod(g,114) as id_doc14, mod(g,115) as id_doc15, mod(g,116) as id_doc16, md5(g::text) as workload from pg_catalog.generate_series(1,1000000) g ;
Создадим на этой таблице некоторое количество индексов:
create index test_fastpath_main_ix1 on test_fastpath_main (id_doc1); create index test_fastpath_main_ix2 on test_fastpath_main (id_doc2); create index test_fastpath_main_ix3 on test_fastpath_main (id_doc3); create index test_fastpath_main_ix4 on test_fastpath_main (id_doc4); create index test_fastpath_main_ix5 on test_fastpath_main (id_doc5); create index test_fastpath_main_ix6 on test_fastpath_main (id_doc6); create index test_fastpath_main_ix7 on test_fastpath_main (id_doc7); create index test_fastpath_main_ix8 on test_fastpath_main (id_doc8); create index test_fastpath_main_ix9 on test_fastpath_main (id_doc9); create index test_fastpath_main_ix10 on test_fastpath_main (id_doc10); create index test_fastpath_main_ix11 on test_fastpath_main (id_doc11); create index test_fastpath_main_ix12 on test_fastpath_main (id_doc12); create index test_fastpath_main_ix13 on test_fastpath_main (id_doc13); create index test_fastpath_main_ix14 on test_fastpath_main (id_doc14);
В данный момент у нас есть 15 индексов (не забываем про primary key созданный при создании таблицы)
Запустим тест, используя "кастомные" pgbench-скрипты.
Тестовый скрипт (main.sql) выглядит так:
\set p_id random(1, 10000) select * from test_fastpath_main where id=:p_id;
Мы читаем одну запись по случайному первичному ключу.
Далее запускаем тест, используя утилиту pgbench (в режиме "кастомных" скриптов).
pgbench -f main.sql -n -j 150 -c 150 -t 100000 /*-n не запускать вакуум на стандартных таблицах pgbench -j количество потоков -с количество "коннекций" -t количество транзакций */
Теперь посмотрим паттерн нагрузки, используя расширение pg_wait_sampling от pgPro (можно использовать и просто сэмплирование pg_stat_activity).
SELECT date_trunc('second',ts) tm_sec, h.event_type, h.event, count(1) FROM pg_wait_sampling_history h right join pg_stat_activity a on (a.pid=h.pid) where 1=1 and a.application_name ='pgbench' group by date_trunc('second',h.ts), h.event_type, h.event order by 1 desc, count(1) desc, 4 desc; tm_sec |event_type|event |count| -----------------------------+----------+----------+-----+ 2026-02-07 20:54:21.000 +0300|Client |ClientRead| 411| 2026-02-07 20:54:21.000 +0300| | | 39| 2026-02-07 20:54:20.000 +0300|Client |ClientRead| 3811| 2026-02-07 20:54:20.000 +0300| | | 367|
Видно нормальную "здоровую" картинку. Сессии в базе либо проводят время на CPU, либо ждут данные от клиента (ClientRead).
Теперь давайте добавим ещё 2 индекса:
create index test_fastpath_main_ix15 on test_fastpath_main (id_doc15); create index test_fastpath_main_ix16 on test_fastpath_main (id_doc16);
И пер��запустим точно такой же тест - видим, что паттерн ожиданий кардинально поменялся,
в топ ворвалось событие LWLock:LockManager.
tm_sec |event_type|event |count| -----------------------------+----------+-----------+-----+ 2026-02-07 21:12:20.000 +0300|LWLock |LockManager| 1891| 2026-02-07 21:12:20.000 +0300|Client |ClientRead | 1458| 2026-02-07 21:12:20.000 +0300| | | 247| 2026-02-07 21:12:20.000 +0300|Timeout |SpinDelay | 4| 2026-02-07 21:12:19.000 +0300|LWLock |LockManager| 597| 2026-02-07 21:12:19.000 +0300|Client |ClientRead | 379| 2026-02-07 21:12:19.000 +0300| | | 72| 2026-02-07 21:12:19.000 +0300|Timeout |SpinDelay | 2|
Мы добавили всего 2 дополнительных индекса к существующим 15-ти, и, казалось бы, это вообще никак не должно повлиять на операцию SELECT по первичному ключу, но повлияло.
В чём же секрет подобного поведения?
Довольно хорошее описание события ожидания LWLock:LockManager есть на сайте AWS
(описание относится к немного другому варианту СУБД Postgres, но основные механизмы остались теми же самыми, что для "ваниллы", что для pgPro).
Вкратце, в PostgreSQL есть механизм быстрых блокировок fast path locking, в частности, он активно используется при выборке данных SELECT'ом (защищают нижележащие объекты от изменения структуры в момент выполнения запроса), но есть ограничения по масштабированию данного механизма.
"Когда количество запрошенных записей о блокировках для одного и того же серверного процесса превышает 16, что соответствует значению FP_LOCK_SLOTS_PER_BACKEND, менеджер блокировок не может использовать метод fastpath locking", Cм. также данный ресурс (я привожу код для мн��й актуальной 16ой версии, в 18ой произошли изменения данного механизма к лучшему). FP_LOCK_SLOTS_PER_BACKEND это константа времени компиляции (в файле storage/proc.h).
Таким образом, мы предполагаем, что во втором случае мы переключились с механизма fastpath locking, на традиционный, более тяжёлый механизм для LWLock.
Давайте посмотрим на состояние блокировок в момент нашего теста:
select * from ( select lk.pid, count(1) over(partition by lk.pid) total_cnt, count(1) filter (where lk.fastpath) over(partition by lk.pid) as cnt_fast, count(1) filter (where not lk.fastpath) over(partition by lk.pid) as cnt_notfast, relation::regclass AS relationname, locktype || ' : ' || mode AS lockdef, a.wait_event_type || ':' || a.wait_event as wait, lk.granted, lk.fastpath , a.state from pg_locks lk inner join pg_catalog.pg_stat_activity a on lk.pid=a.pid where 1=1 and relation is not null and a.application_name like 'pgbench' order by pid desc,relation) a 1981808| 18| 16| 2|test_fastpath_main |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_pkey|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix1 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix2 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix3 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix4 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix5 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix6 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix7 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix8 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix9 |relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix10|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix11|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix12|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix13|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix14|relation : AccessShareLock|LWLock:LockManager|true |true |active| 1981808| 18| 16| 2|test_fastpath_main_ix15|relation : AccessShareLock|LWLock:LockManager|true |false |active| 1981808| 18| 16| 2|test_fastpath_main_ix16|relation : AccessShareLock|LWLock:LockManager|true |false |active|
Видно, что мы взяли 16 блокировок, используя fastpath lockingмеханизм (сама таблица, PK и 14 индексов) и 2 блокировки не используют fastpath (это 2 индекса созданных последними).
Можем считать нашу гипотезу доказанной.
Итого: избыточное количество индексов может привести к проблемам производительности даже для выборки дан��ых.
Что делать: Перепроверить и удалить неиспользуемые индексы либо страдать, либо быстренько переходить на 18ую.
NB
Если в вашем запросе более 1 таблицы, то посчитаются все таблицы, участвующие в запросе (а также их индексы), но есть и хорошие новости: сама блокировка LWLock:LockManager берётся довольно быстро и эффект для сложных многоуровневых запросов будет почти незаметным.
Однако для простых запросов, вроде рассмотренного запроса по первичному ключу, эффект может быть ощутимым, измерения на данном тестовом примере на моём железе дали эффект ~30%.
