Задать вопрос
@deliro
sql

Как заставить Postgres использовать индексы?

Всем привет. Имеется вот такая таблица:
CREATE TABLE public.core_entry (
  id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('core_entry_id_seq'::regclass),
  keyword CHARACTER VARYING(100) NOT NULL,
  created TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE INDEX core_entry_e2fa5388 ON core_entry USING BTREE (created);
CREATE INDEX keyword_gist_idx ON core_entry USING GIST (keyword);

GiST индекс работает очень хорошо, но проблема в том, что он не работает на EXACT, т.е. при запросе
SELECT keyword FROM core_entry WHERE keyword='something';

Происходит Seq Scan
Да, это можно подстроить, написав
SELECT keyword FROM core_entry WHERE keyword LIKE 'something';

И результат будет такой же, но с применением индекса.

Главный (самый частый) же запрос выглядит вот так:
SELECT keyword, count(keyword) as count FROM core_entry WHERE keyword LIKE '%something%' GROUP BY keyword;

В этом случае функция count() начинает Seq Scan (судя по всему, проверяя равенство).
Тут я попробовал добавить обычный индекс:
CREATE INDEX keyword_idx ON core_entry USING BTREE (keyword);

И... Всё стало ещё хуже. Теперь он спокойно справляется с запросом
SELECT keyword FROM core_entry WHERE keyword='something';

Но LIKE '%something%' он обрабатывает последовательным чтением.

Вопрос:
Как изменить запрос (желательно удалив BTREE индекс, ибо в базу попадает 10млн записей в неделю), чтобы count(keyword) считался через GiST индекс?
  • Вопрос задан
  • 2796 просмотров
3 комментария
Подписаться 3 Оценить 3 комментария
Решения вопроса 1
@kshvakov
Сделайте 2 таблицы, что то вроде 

create table core_keywords(
    keyword_id serial primary key,
    keyword    varchar(100)
);
create unique index u_idx_keyword on core_keywords(lower(keyword));
create index t_idx_keywords on core_keywords using gin (lower(keyword) gin_trgm_ops);

create table core_keywords_entry(
    keyword_id int not null references core_keywords,
    created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
    primary key (keyword_id, created_at)
);


ну и будет все проще, что-то вроде 

select 
    e.keyword_id, 
    count(*) 
from core_keywords_entry as e
join core_keywords as k using(keyword_id)
where lower(k.keyword) like  '%something%'
group by 1
Ответ написан
11 комментариев
11 комментариев
Пригласить эксперта
Ответы на вопрос 1
@lega
А что если keyword сделать массивом (или использовать tsvector), тогда должен работать поиск на точные совпадения, а значит (возможно) и заработает группировка. В монге так "облако тегов" работает нормально.
Ответ написан
Комментировать
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Тестировщик SQL
Bell Integrator • Санкт-Петербург
До 200 000 ₽
Программист SQL
АвтоАльянс • Москва
от 165 000 ₽
Программист SQL
САМО-Софт • Москва
До 220 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Разработка дизайна раздела «Статьи» на сайте «Мир отходов»
23 апр. 2024, в 23:01
10000 руб./за проект
Дизайн личного кабинета (клиентская часть)
23 апр. 2024, в 22:37
500 руб./в час
Скрипт для Solana быстрый
23 апр. 2024, в 22:33
25000 руб./за проект
Ещё заказы