Всем добрый день. Как построить индекс, чтобы запрос работал быстрее??

-- установил расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- построил составной gin индекс
DROP INDEX IF EXISTS idx_gin_svjul_ltrim_inn_id;
CREATE INDEX IF NOT EXISTS idx_gin_svjul_ltrim_inn_id ON svjul USING gin (ltrim(data ->> 'INN') gin_trgm_ops, id);

-- запрос к таблице (размер более 100 Гб)
SELECT * FROM svjul svjul1 WHERE ltrim(svjul1.data ->> 'INN') ilike '7839074221%'
ORDER BY svjul1.id ASC LIMIT 20 OFFSET 0;

-- ожидаемый результат: select выше работает быстро
-- фактический результат: select выше работает мелденно и в плане запроса не видно срабатывания индекса

уберите звёздочку
уберите ltrim
Вытащите INN в отдельное поле таблицы

(1) Ни запрос, ни структуру таблицы менять нельзя. В этом то и прикол.

В SELECT * FROM svjul svjul1

svjul и svjul1 = это две таблицы
или svjul1 - алиас таблицы svjul ?

(0) хоть бы сказал, что за СУБД.

(3) очевидно да
(4) PostgreSQL, судя по префиксу "pg" и попытке индексировать json-поле

(5) тогда где комментарий про VACUUM FULL?

(0) Нужен explain без этого никак

(3) (4) подтверждаю слова (5)

(6) появилось предположение, что постгрес при сортировке по id решает использовать первичный ключ вместо индекса (коим id является)

Index Scan using svjul_pkey on svjul as svjul
Filter: (ltrim((data ->> 'INN'::text)) ~~* '7839074221%'::text)

сейчас попробую изменить order by, может быть подмена первичного ключа спасет отца русской демократи

(6) (5) я в (8) был прав, стоило мне заменить в секции ORDER BY id на другое поле и запрос заработал быстро!!

SELECT * FROM svjul WHERE ltrim(data ->> 'INN') ilike '7839074221%'
ORDER BY created ASC LIMIT 20 OFFSET 0;

А какое количество строк он возвращает и ожидает вернуть, где оценки?

(10) По одному ИНН 1-3 записи, LIMIT 20 стоит ограничитель, вот только без составного индекса этот ограничитель будет работать медленно. Видимо придется добавить колонку id1, которую сделать первичным ключом, а по id построить просто индекс. Не знаю прокатит ли такое.

(10) У вас какой-то кастрированный explain.

Вот у меня какой (из заббикса табличка)

explain select * from history where itemid = 42259 order by clock limit 20;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
Limit  (cost=0.43..73.34 rows=20 width=24)
   ->  Index Scan using history_pkey on history  (cost=0.43..53292.28 rows=14620 width=24)
         Index Cond: (itemid = 42259)
(3 rows)

(12) суть проста - scan using svjul_pkey вместо нужного мне индекса вот и всё

Нихера не понятно, но очень интересно.
Руки бы оторвать этим любителям сокращений.

select * - вот причина.
Поменяй на select INN

(14) это мимо

Ответ в (9). Другой вопрос как элегантнее решить проблему

(15) Как скажешь

Если считаешь, что твоя сортировка волшебным образом заставляет использоваиь некластерный индекс - ну так выбери во временную таблицу, а потом досортируй как надо.

Скорее всего ты просто прогрел кэш и запрос все также шарашит по кластерному индексу, ведь ему нужны остальные поля для select *, которых нет в некластерном и за которыми надо сходить по ridlookup. Да, limit скорее всего позволит это сделать только не более 20 раз, но так ли умен оптимизатор pg.

(18) запрос менять нельзя в том то и проблема

(20) конкурсы все интереснее. Ну ты план запроса то хоть собери для
1) ORDER BY created
2) Select inn

Чтобы понимать картину

(20) добавь в твой индекс все поля запроса по include

**все поля таблицы

Каким местом извлечение из jsonb в текст, затем ltrim а затем еще и ilike
Может работать быстро?

(0) проблема в том что не по-русски написано)))

(24) Может, это ведь GIN индекс как раз для ilike. Ну а если бы ilike не было то и btree сгодился бы.

>CREATE INDEX IF NOT EXISTS idx_gin_svjul_ltrim_inn_id ON svjul USING gin (ltrim(data ->> 'INN') gin_trgm_ops, id);

не нравится мне что индекс по вычисляемому полю

как sql поймет что один ltrim(data ->> 'INN' равен другому ltrim(data ->> 'INN' да еще для ilike ?

(27)+ имхо делай ты отдельную табличку с INN и используй ее

(27) Легко понимает, если я меняю первичный ключ с id на другую колонку. И запрос из 0 работает быстро. Вот только теперь выяснено, что приложение каким то боком подхватывает новый первичный ключ строит по нему другой order by и снова получается зависание. Получается замена первичного ключа не катит. Как-то статистику покрутить может можно??

(29) к (28)

(28) к сожалению запрос формирую не я, а стороннее приложение... так бы давно перекроил запрос и забыл как страшный сон

а если убрать id?

CREATE INDEX IF NOT EXISTS idx_gin_svjul_ltrim_inn_id ON svjul USING gin (ltrim(data ->> 'INN') gin_trgm_ops);

(32)+ гм не понял а gin_trgm_ops это что?

(33)+ понял указание на триграмный

(32) тогда он сможет ускорить запрос без order by id - не катит
(34) полнотекстовый мудреж

(35) опечатка, наоборот НЕ сможет

Из за order by id

сделай view

А может попробовать убрать "USING gin"?
Вроде этот индекс не так работает, как хотелось бы (0) https://postgrespro.ru/docs/postgrespro/9.5/datatype-json#json-indexing

(38) не пойдет - приложуха с view работать не сможет
(39) не пойдет - кроме gin с ilike не работет ничего

Как ни странно, я добавил колонку id1, перекинул первичный ключ с id на id1, затем перекинул ключ обратно на id и удалил id1 - в итоге запрос начал подхватывать мой индекс gin вместо первичного ключа, каким образом мои манипуляции могли повлиять знает кто-нибудь?

(40) Хм, проверил онлайн запросом, ilike сработал без gin.
Откуда сведения? В документации нет таких ограничений https://postgrespro.ru/docs/postgresql/9.6/functions-matching

(41) он сработал, но только на больших объемах то медленно будет, в нашем случае это = не работает

Удалось добиться ускорения поиска по ИНН вот такими заклинаниями:
CREATE INDEX IF NOT EXISTS idx_gin_svjul_ltrim_id_inn ON svjul USING gin (id, ltrim(data ->> 'INN') gin_trgm_ops);
ALTER INDEX idx_gin_svjul_ltrim_id_inn ALTER COLUMN 2 SET STATISTICS 1000;
ANALYZE svjul;

(38) (39) (21) (10) (1) благодарю за содействие. Хотелось бы поподробнее до каких пор эта штука у меня проработать может ещё и если недолго то как продлить?

(44) Многопоточность как-нибудь применяется? Скажем, max_parallel_workers_per_gather=6
и в плане запроса
Workers Planned: 6
   Workers Launched: 6
   ->  Parallel Seq Scan и т.д.

(45) Даже и не знаю. А как это на план запроса влияет?

(46) Здесь есть примеры https://habr.com/ru/articles/305662/
Статья старенькая, но все же..