Какие есть лучшие практики для ускорения поиска по text в Postgresql?

Question

[Владимир]

Создал очень большую таблицу на 50Gb с столбцом path (путь в файловой системе) с типом text. Как организовать поиск по этому столбцу с максимальной скоростью?
Пробовал просто накинуть индекс но не особо помогает. Что можно сделать в принципе?

Comments

[Ипатьев]

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

какого рода "поиск"?
что за индекс?
что значит "не особо помогает"?

[Slava Rozhnev]

перейти с PostgreSQL на Elastic, SphinxSearch.com или что-то подобное

Answer 1

[Ипатьев]

Если отвечать не на конкретный вопрос, а говорить про абстрактные "лучшие практики", то
1. Убедиться что на сервере достаточно памяти для индекса. Иначе его просто бесполезно создавать.
2. Индекс помогает только если искать либо точное совпадение, либо частичное от самого начала значения. Если поиск нужен по подстроке, то надо придумывать что-то ещё. Что конкретно - зависит от поисковых запросов.

Comments

[Владимир]

Ну если конкретезировать, то да поиск по подстроке по regexp.

Path соответсвенно некоторая строка которая на самом деле запись иерархии файловой системы. Возможно для такого случае есть смысл использовать ltree?

[Ипатьев]

regexp всегда будет медленно
об этом надо было обязательно написать в вопросе
приведя пример своего поиска

в общем случае же можно только порекомендовать умерить аппетиты, и искать хотя бы по именам каталогов целиком

[Владимир]

Ну вопрос даже не в этом, я просто не верю что я единственный кто столкнулся с задачей хранить пути в ФС и оперировать ими. (выбрать все глубже третьего уровня например, или все каталоги на 2 уровне начинающиеся с определённого слова).

И при этом с этим нельзя быстро работать

Пример запроса привести

select path,substring(path,'^(/([a-zA-Z0-9]|\.|_|-)*/)') n_p
from(select path, filesize, 
substring(path, 
'((?<=^)(/inbox(_sec)?/|/warehouse/tablespace/(external|managed)/bive/|/staging(_sec)?/|/apps/spark/warehouse/|/tablespace/external/bive/|/archive(_sec)?/|/rejected(_sec)?/|/user/)(([a-zA-Z0-9]|\.|_|-)*))') new_path
from public.fsimage) a
where new_path is null

[Ипатьев]

ну это как раз элементарно.
можно вручную сделать аналог полнотекстового поиска - таблицу где будут отдельные каталоги в древовидной структуре
можно воспользоваться готовыми решениями.
только поиск по дереву - это совсем, совсем, СОВСЕМ не "поиск по text", который в исходном вопросе.

[Владимир]

Что значит то "вручную сделать аналог полнотекстового поиска"?

я не зря указал в запросе что у меня это путь в файловой системе. За не имением ничего лучшего в PG

[Ипатьев]

там дальше написано. буквами по экрану