10к строк по базе размером 500кк?

Question

[setupx]

Есть вот такая база

CREATE TABLE base
(
    id       BIGSERIAL PRIMARY KEY,
    url      text NOT NULL,
    login    text NOT NULL,
    name text NOT NULL
);

Какими способами можно ускорить поиск по совпадениям url/login/name?
Некоторые индексы вообще отказываются применяться: размер строки индекса (3432) больше предельного для btree версии 4 размера (2704) (индекс "idx_base_url") (SQLSTATE 54000)

Условно в БД есть записи в url со словом login: "cloudflare.com/login", "login.test.com", и мне нужно реализовать поиск по таким столбцам и на выходе получить не более 10к строк не более чем за 10 секунд.

P.S: если нужно: на бэке используется Golang
P.S2: кэшировать данные не вариант, т.к после поиска строк человеком, эти строки удаляются

Comments

[Akina]

поиск по совпадениям url/login/name

Пожалуйста, покажите конкретный запрос (или запросы), о котором идёт речь.
Также укажите максимальный размер данных в каждом из полей (в байтах).

PS. Если поиск идёт по точному совпадению - то индексируйте хэш и ищите по нему. Ну и проверяйте на коллизии, если хэш короткий.

[setupx]

Akina, Запрос

SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;

Про размеры точно сказать не могу.
Текущая БД, которая используется:

CREATE TABLE base
(
    id       BIGSERIAL PRIMARY KEY,
    domain   VARCHAR(255) NOT NULL,
    uri      TEXT         NOT NULL,
    login    text         NOT NULL,
    name   text         NOT NULL
);

[Dmitry Bay]

setupx,
А почему у вас
uri / login / name - это TEXT?
при этом domain - varchar ?

[setupx]

Dmitry Bay, домены же не такие большие. впринципе проблема решена. спасибо

Answer 1

[Akina]

Запрос

SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;

Какой это нахрен поиск "по совпадениям"??? Это, блин, поиск по подстроке!!! причём в любом месте строки.

С таким шаблоном поиска можете с обычными индексами вообще не трахаться, по причине абсолютной бессмысленности действа. Читайте про GIN, GiST, RUM и прочие виды индексов и поиск с их использованием.

Answer 2

[galaxy]

SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;

проще всего так, наверно:

CREATE EXTENSION pg_trgm;

CREATE INDEX trgm_domain_base_idx ON base USING GIST (domain gist_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;

https://www.postgresql.org/docs/current/pgtrgm.html

Answer 3

[ky0]

Первое, что приходит в голову - разделить домен и URI. Тогда можно будет сначала поискать по индексу доменов, а потом уже эту выборку по урлу.

Comments

[setupx]

А вот это интересно, сейчас попробую так сделать, навесить индексы и провести поиск

Answer 4

[Антон Антон]

индекс по like %login% не будет применяться. нужно добавить колонку типа булево и partial index на неё. При вставке/обновлении и один раз при создании колонки - заполнить её значением true для нужных строк.

Comments

[setupx]

не совсем понял. можно кусок кода?

Answer 5

[rPman]

Избавляйся от like '%...%', это худший способ искать, он индексы не использует
даже поиск с регулярными выражениями работает лучше (правда там лимит на размер строки)

Если like используется чтобы искать части url, то храни в базе не целиком а разделенную по полям, в идеале чтобы поиск был на равенство (даже если база увеличится и станет сложнее, например тебе нужно искать по параметрам GET url, т.е. потребуется создать еще одну таблицу на них, скорость все равно будет хорошей)

Answer 6

[Makcimmm]

А мне первое что приходит на ум, реализовать собственный словарик + табличку в которой будут указаны сопоставления ID слов из словарика и ID из таблицы с текстом в которых встречается данное слово.
Я так делал, когда на проекте поиск был по большим кускам текста, который был в BLOB-ах. Я разделял текст на слова по пробелам.
Использовали этот поиск редко, но когда запускали, он висел минут 10-20, люди уходили пить чай наверно :) и всех это устраивало. Но однажды этот поиск повесил БД, и эта проблема оказалась в поле моего зрения. После формирования словарика и добавления функций чтобы по новым записям так же добавлялась инфа в словарик, поиск стал занимать доли секунды :)