Всем привет. У меня есть локальное окружение с постгрессом, там

находится чуть больше 1 млн строк. Сама таблица представляет из себя нечто следующее:


CREATE TABLE my_table (
id NOT NULL PRIMARY KEY,
...
search_title VARCHAR NOT NULL,
another_search_field VARCHAR NOT NULL,
deleted TIMESTAMP
)


Другие поля просто опустил за ненадобностью в данном вопросе. Так вот, у меня там был обычный составной индекс для (search_title, another_search_field). Начал анализировать планировщика запросов и понял, что индексы у меня не используются. Важно уточнить, что у меня в запросах имеется LIKE оператор, т.е это выглядит примерно так:


SELECT * FROM my_table WHERE search_title LIKE '%some title%' AND another_search_field = '...';


В общим, анализ (EXPLAIN (analyze,verbose,timing,costs,buffers)) мне показывает, что у меня используется последовательный поиск для запроса

Запрос:


SELECT id FROM my_table WHERE search_title LIKE '%title%'


Результат:


Seq Scan on public.my_table (cost=0.00..40873.66 rows=1052908 width=16) (actual time=0.050..7154.845 rows=1053013 loops=1)
Output: id
Filter: ((mt_table.search_title)::text '%title%'::text)
Buffers: shared hit=192 read=27519
Planning Time: 0.069 ms
Execution Time: 13513.829 ms



На всякий случай отключил последовательную прогонку, но в итоге результат всё равно такой, что индекс не используется. Понятное дело, что set enable_seqscan = off не форсит отмену последовательной прогонки. Но, похоже, что других вариантов нет для планировщика. И в том числе нет варианта использовать индекс.


Seq Scan on public.my_table (cost=10000000000.00..10000040873.66 rows=1052908 width=16) (actual time=24.158..7218.534 rows=1053013 loops=1)
Output: id
Filter: ((my_table.search_title)::text '%title%'::text)
Buffers: shared read=27711 written=32
Planning:
Buffers: shared hit=11 read=5 dirtied=3 written=5
Planning Time: 0.730 ms
JIT:
Functions: 4
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 1.118 ms, Inlining 4.890 ms, Optimization 12.192 ms, Emission 6.884 ms, Total 25.084 ms"
Execution Time: 13776.353 ms


В итоге подумал, что проблема в том, что обычный индекс тут не особо уместен, попробовал создать GIN индекс. Потому что исходя из моих требований (поиск по LIKE) все филды текстовые, а значит и уместность, по идеи, должна быть.


CREATE INDEX trgm_idx ON my_table USING GIN (search_title gin_trgm_ops) WHERE deleted IS NULL;
Касаемо deleted, то у меня просто используется принцип soft-delete в приложении.


Короче, пробую опять. И результат тот же - индекс не используется. В итоге у меня сложилось ощущение, что я что-то упустил. Может GIN индекс тут неуместен? Может тестовые данные у меня не особо подходящие? Про второе следует уточнить, что я нагенерил 1мл записей, где в качестве поля search_title выступает следующий паттерн: UUID title UUID. Т.е условно говоря, должно быть большое кол-во страниц с повторяющимися данными для LIKE оператора. Потому что сейчас, что индекс есть (обычный или GIN), что его нет - одна репка, как говорится.

Ты что-то упустил. Позже скажу, что

Интрига. Интересно узнать что именно упустил.

Для начала ты упустил, что индекс не должен всегда обязательно использоваться. Он используется только, если с ним запрос будет "быстрее" (на самом деле — будет с меньшей стоимостью выполнения)

Далее: Что является SARG-ом, а что нет: search_title LIKE '%some title%' - не SARG another_search_field = '...' - SARG Cледовательно SELECT id FROM my_table WHERE search_title LIKE '%title%' этому запросу индекс в принципе не нужен. Разве что использовать как покрывающий. Будет table scan Кажется, на все твои вопросы ответил.

SARG - это Search ARGument , выражение, которое может быть использовано для поиска (фильтрации) по индексу.

индекс для (search_title, another_search_field). — сюда не подходит никак, потому как ведущее поле не SARG. (не входит в SARG) Тут подошёл бы потенциально индекс (another_search_field) , но там надо ещё статистику смотреть

Да, возможно, что в твоей ситуацыи тэстовые данные не особо подходящие либо индэкс GIN неуместен. С другой стороны -- возможно, что вы что-то нетак делаете в запросе. Тем более, что вроде при похожых ситуацыях GIN-индэкс должэн использоваться. Точнее можно будет начать анализировать если вы покажыте точный \d для таблицы и запрос, а не какие-то странные и несовместимые друг с другом отрывки.

Почему отрывки несовместимы? Я показал реальные запросы. Т.е у меня поиск по LIKE у текстового поля.

Звучит разумно, но есть одно НО: https://niallburkley.com/blog/index-columns-for-like-in-postgres/ Почему в данной ситуации запрос смог стать SARG? И как его сделать таким? Потому что если я буду использовать не LIKE, а сравнение, то индекс все равно не будет использоваться.

Нет, дело не в данных

Я даже не буду ссылку смотреть, по LIKE индекс может использоваться , если в LIKE задаётся ПРЕФИКС значения. Если задаются инфиксы или суффиксы - то нет. У тебя задаётся ИНФИКС.

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

А чем статья отличается от ответа на форуме или в чатике? Вопрос риторический. Филькина это грамота - уже другой вопрос. Можно тогда указать пункт в доке, где написано про совместимость LIKE оператора в контексте GIN индекса?

А можно поподробнее?

Заглянул таки в статью, Postgres uses trigrams to break down strings into smaller chunks and index them efficiently. The pg_trgm module supports GIST or GIN indexes and as of Postgres version 9.1 these indexes support LIKE/ILIKE queries. Если с триграммами, то это , конечно, другое совсем дело, но у тебя ТАКОЙ индек был построен?

И кстати - только сейчас заметил — ты если пишешь в индексе CREATE INDEX trgm_idx ON my_table USING GIN (search_title gin_trgm_ops) WHERE deleted IS NULL; то то же условие надо повторять и в запросе. Иначе индекс не сможет быть задействован.

>USING GIN (search_title gin_trgm_ops)

Откуда я знаю, почему у тебя получились несовместимые отрывки?

Откуда я знаю, что ты понимаешь под несовместимыми отрывками?

Хм, хороший поинт про условие индекса, да. Запрос стал пошустрее в два раза. Но теперь он решил выбрал finalize aggregate. И да, про условие с soft-delete вылетело из головы, так как запрос делается другой библиотекой, там у нас своя модификация JPA.

Как минимум -- твой запрос несовместим с индэксом WHERE deleted IS NULL.

Но вообще -- важнее что отрыки, которые трудно проверить, а не то, что они несовместимы.

Ну да, про этот момент уже упомянул @MasterZiv , за что ему спасибо. Оказывается, в моей версии jpa затерся необходимый soft-delete предикат (where deleted is null). Но в остальном то что не совместимо? Хочется услышать конкретику.

Лучше выкинь ты этот where deleted is null потому что у тебя 90% данных будет под это подходить, нет смысла. Вот если делать индекс наоборот для поиска среди удалённых, там это может помочь.

Фиг его знает -- в остальном можэт и совместимо, однако проверять на отрыках всё равно малореально.

Если бы я мог выкинуть. Этот геморрой идёт не от меня, увы. И вечно подобные выстрелы в колено происходят, если честно.

Из индэкса выкинь.

Мы же тут не в клубе философов, которые обсуждают высокую материю. Кажется, проблему уже нашли. А не "ну у тебя запрос несовместим, а почему - ну фиг знает" 😉

Если вы тут не в клубе философов -- то это вашы личные трудности.

Индексы создаются этой же либой.

Ну, это... Ну, у меня слов нет. В общем, ты можэшь жаться, секретничать и устраивать тут ЧГК сколько твоей душэ угодно -- но помешает добиться результата это именно тебе, если что.

Неужели ж JPA додумывается до того, чтобы на конкретной платформе протянуть в индекс условие WHERE ?

Слушай, ну есть же такая штука как NDA. Я считаю, что описал необходимый минимум. Если не согласен - то уточняй тогда где именно, я дополню вопрос.

В триграм индэкс, хе-хе. До чего дошёл противоестественный интеллект!

Проблема совсем не в этом, в том, что ты пока не понимаешь, когда и зачем в СУБД используются индексы. Ты думаешь — индекс - значит, будет быстро. Это не так. Вот проблема. И мы её не решили.

Там ко всему условие с soft-delete применяется. В общем, суть в том, что индексы не должны действовать на те сущности, которые "удалены". Т.е у которых поле deleted проставлено.

Ну, ты сейчас под своё NDA начинаешь подтягивать вызовы своей "библиотеки", которые создают индэксы. Не, опять жэ, флаг в руки...

И что за JPA там у вас?

Так. Допустим. >Вот проблема. И мы её не решили А что ты именно хочешь решить?

Кончайте сраться.

К тебе тоже относится

И да, если всё плохо с NDA -- ну, создай testcase искуственный чтобы своё понимание прокачать. В общем, у людей жэлания париться и угадывать что там у тебя образовалось между многоточиями и что ты отрезал от какого запроса -- будет гораздо меньшэ, чем просто повторить какой-то случай и посоветовать, что происходит.

Это резонное утверждение.

Не понимаю о чем ты говоришь. Я лишь отвечаю на вопросы, которые относятся к моему первому вопросу. Т.е у меня нет цели разводить смуту. А именно разобраться.

Я могу и вообще отсюда выйти, чотам. Мест, где удаль молодецкую показать пока хватает.

Александр, ну ладно, вопрос решили? Всё понял?

Да нет, я б не хотел...

Да, конечно. За это и спасибо :)

Ты там спроси у того, кто такое "условие" ставит -- он головкой в детстве не стукался? Нахера он своими политическими требованиями лезет в потроха СУБД, в разработке которых он, судя по всему, ничего не понимает?

Это может делать библиотека - реализация JPA автоматом, без программирования.

Да не важно, кто физически это делает -- библиотека или программист. Важно -- почему политические вопросы просочились настолько в нижний уровень системы хранения.

Ты ещё учти, что JPA можно наконфигурировать так, чтобы индексы оно не создавало вообще, и создавать их руками.