postgresql: правильно построить индексы для ускорения выполнения запроса с group by

Рейтинг: 2Ответов: 1Опубликовано: 03.06.2023

в таблице data_t есть поле words, содержащее фразы, разделённые запятой:

word1, word2, word3 and word4, other words

требуется получить по таблице статистику какие фразы сколько раз встречаются в записях

написал такой запрос:

SELECT 
        COUNT(*) AS count, word AS value
FROM (
    SELECT 
        trim(regexp_split_to_table(words, ',')) AS word
    FROM 
        data_t
) AS t
GROUP BY word

всё хорошо, но не устраивает скорость работы запроса, когда выбирается из таблицы много записей

подскажите, можно ли его ускорить?

поле words не индексировано и думаю, что построив правильные индексы можно решить проблемы, вопрос лишь в том, какие индексы нужны?

или индексирование не поможет? если смотреть в сторону предобработки (например создать специальный столбец prepared_words), то какие данные в нем должны храниться? массив предварительно выделенных слов (что даст экономию времени на использовании trim и regexp_split_to_table?

sql postgresql оптимизация index оптимизация-запросов
Источник: Stack Overflow на русском

Ответы

▲ 2

Поле words содержит фразы, разделенные запятыми, создание обычного индекса на этом поле не даст значительного улучшения производительности.

Могу предложить следующие варианты ускорения скрипта:

а. Создать индекс на выражение.

CREATE INDEX words_expr_idx ON data_t (trim(regexp_split_to_table(words, ',')));

б. Создать функциональный индекс.

CREATE INDEX words_func_idx ON data_t ((regexp_split_to_table(words, ',')));

в. Дообработать поле words в prepared_words для получения статистики. Например, так:

SELECT 
    COUNT(*) AS count, word AS value
FROM (
    SELECT 
        regexp_substr(prepared_words, '[^ ]+', 1, LEVEL) AS word
    FROM 
        data_t
    CONNECT BY regexp_substr(prepared_words, '[^ ]+', 1, LEVEL) IS NOT NULL
) t
GROUP BY word;

г. Кэшировать результаты запроса, но это не для всех целей может помочь.