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

Question

[Roman K]

Всем привет. Имеется вот такая таблица:

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 индекс?

Comments

[Артём Клименко]

>SELECT keyword, count(keyword) as count FROM core_entry WHERE keyword LIKE '%something%';
как понимаю тут group by пропущен?
а какая еврсия постгре у вас? Поскольку мне при всём желании не удалось аналогичном запросе сделать(postgre9.4) так чтоб индекс не использовался.
И на всякий случай analize у вас по этой табличке давно происходил?

[Roman K]

Артём Клименко: Да, забыл написать group by keyword. Версия 9.3. Индекс при поиске ключей используется, а при их подсчёте - нет.

[Roman K]

Артём Клименко: Вот EXPLAIN запроса

SELECT keyword, count(keyword) as count FROM core_entry WHERE keyword LIKE '%вк%' GROUP BY keyword ORDER BY count DESC LIMIT 10;

Answer 1

[Кирилл]

Сделайте 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

Comments

[Roman K]

Вариант. Но мне важна скорость инсерта, а проверять каждый раз на существование keyword'а в таблице, кажется, будет затратно (даже если ловить эксепшн)

[Кирилл]

Roman Kitaev: не затратно будет, там uniq индекс

[Roman K]

Кирилл: А как тогда быстрее будет навскидку: проверка на существование и создание или просто создавать и пропускать эксепшн?

[Roman K]

Кирилл: И, кстати, так я не смогу вставить в таблицу сразу, допустим, тысячу ключей. Только по одному.

[Кирилл]

Roman Kitaev: можно и тысячу сразу вставить в постгресе есть array ;)

[Roman K]

Кирилл: Я имею ввиду, что будет unique_violation

[Кирилл]

Roman Kitaev: в качестве намёка:

truncate core_keywords;

insert into core_keywords ( keyword)
select k from (select distinct unnest('{a,b,c,d,a,b,c,d}'::varchar[]) as k) _
where not exists (select 1 from core_keywords as k2 where k2.keyword = k);

insert into core_keywords_entry (keyword_id)
select
k.keyword_id
from core_keywords as k
join (select unnest('{a,b,c,d,a,b,c,d}'::varchar[]) as kw) as k2 on k.keyword = k2.kw;

;)

[Roman K]

Кирилл: Спасибо. Я с чистым SQL и особенностями постгреса столкнулся неделю назад. Ещё слишком ноль в этом :)

[Кирилл]

Roman Kitaev: "подтягивайте" его (sql), сильно поможет ;)

[Roman K]

Кирилл: Да я уж заметил. Когда переписал три запроса (которые выполнялись за 20 секунд) в одну функцию, которая выполняется 0.2 секунды.

[Кирилл]

Roman Kitaev: это правильно, чем ближе к СУБД тем надежнее и производительнее, чем в приложении +100500 запросов туда-сюда гонять

Answer 2

[lega]

А что если keyword сделать массивом (или использовать tsvector), тогда должен работать поиск на точные совпадения, а значит (возможно) и заработает группировка. В монге так "облако тегов" работает нормально.