Как бы вы реализовали вычисление списка «любимых эмодзи» пользователя?

Question

[EchoStan]

Здравствуйте, друзья.
В инпуте чата полезно иметь строчку с любимыми эмодзи пользователя. Вроде такой:


Как сейчас это реализовано у меня
0. Для хранения любимых эмодзи пользователя используем отдельную небольшую таблицу:

CREATE TABLE top_used_emojis (
    user_id BIGINT PRIMARY KEY,
    emojis TEXT[] NOT NULL
);

1. Пользователь отправляет новое сообщение в чат.

2. Сообщение сохраняется в таблицу сообщений. Пусть будет chat_messages.

CREATE TABLE chat_messages (
    user_id BIGINT NOT NULL,
    text TEXT,
    ...
)

К таблице добавлен простейший индекс, на бумаге он обещал облегчать поиск по тексту.

CREATE INDEX chat_messages_text_index ON chat_messages (text);

3. В коде приложения выполняется простой поиск совпадений для того, чтобы определить: есть ли вообще в новом сообщении эмодзи.

4. Если эмодзи нет, то ничего не трогаем.

5. Если эмодзи найдены, из приложения вызывается pgplsql-функция, принимающая на вход user_id, вычисляющая топ-10 наиболее часто встречающихся эмодзи в текстах сообщений пользователя и сохраняющая результат в полеemojis таблицы top_used_emojis по ключу user_id.

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

CREATE TABLE emojis (
    emoji TEXT PRIMARY KEY /* Здесь лежит сама эмодзи */
);

А вот и тело функции

CREATE OR REPLACE FUNCTION updateTopUsedEmojis (BIGINT) RETURNS TEXT[] AS '
    DECLARE
        _user_id ALIAS FOR $1;
        query_result TEXT[];

    BEGIN

            WITH last_top_used AS (SELECT emoji, count(*)::INT AS count
                                   FROM chat_messages cm
                                            JOIN emojis e
                                                ON (cm.text LIKE ''%'' || e.emoji || ''%'')
                                   WHERE cm.user_id = _user_id
                                   GROUP BY e.emoji
                                   ORDER BY count DESC
                                   LIMIT 10)
            INSERT INTO top_used_emojis (user_id, emojis)
            VALUES ( _user_id,
                     (SELECT array_agg(emoji) FROM last_top_used)::TEXT[]
            )
            ON CONFLICT (user_id)
            DO UPDATE
            SET emojis = (SELECT array_agg(emoji) FROM last_top_used)::TEXT[]
            RETURNING emojis::TEXT[]
        INTO query_result;

        RETURN query_result;

    END;
'LANGUAGE plpgsql;

6. Всё. На мелких объёмах это работает.

Необходимые уточнения

• Начиная с какого-то момента, мы, понятно, будем выполнять эту процедуру не чаще, чем 1 раз в N времени. При добавлении каждого сообщения - это слишком.
  
• "Любимые" и "наиболее часто встречающиеся" - не совсем одно и то же, но нам норм.
  
• У меня пока некоторые трудности с EXPLAIN - seqscan, вроде, научился распознавать, а косты пока не косты.
  
• Предполагаемое количество пользователей - 1млн, предполагаемое среднее количество сообщений у каждого пользователя - 100
  

Места, которые не дают мне покоя

• Конкатенация в предложении FROM:
  

  FROM chat_messages cm
                   JOIN emojis e
                          ON (cm.text LIKE '%' || e.emoji || '%')

  
  
  Движок DML (или кто там внутре сидит) - он же как-то кэширует результаты этой самой конкатенации?
  Может, стоит заранее заполнить таблицу emojis строчками '%...%'?
  
  
• Оператор LIKE там же. Есть ли более производительные решения?
  
  

Answer 1

[Дмитрий Беляев]

Я бы сделал в таблице top_used_emojis поле хранящее количество использования каждого пересечения user с emoji и повесил бы на это поле DESC индекс а в Primary пихал бы поля user_id и emoji вместе.
На таблицу chat_messages повесил бы тригеры для пересчета количества в top_used_emojis
Ну и выводил бы это простым ORDER BY по индексу с лимитом

Comments

[EchoStan]

Дмитрий Беляев Здравствуйте.

хранящее количество использования каждого пересечения user с emoji и повесил бы на это поле DESC индекс

Я правильно понимаю, что user_id перестаёт быть уникальным ключом в таблице top_used_emojis?
То есть структура таблицы становится такой:

CREATE TABLE top_used_emojis (
    user_id BIGINT,
    emoji TEXT,
    usages_count INT  
);

ALTER TABLE top_used_emojis ADD PRIMARY KEY (user_id, emoji);

CREATE UNIQUE INDEX *** ON top_used_emojis (user_id, emoji, usages_count DESC)

и мы извлекаем значение запросом

SELECT emoji FROM top_used_emojis WHERE user_id = $1 ORDER BY usages_count DESC LIMIT 10?

[Дмитрий Беляев]

EchoStan, да, только второй индекс по 1 полю и не уникальный:

CREATE INDEX *** ON top_used_emojis (usages_count DESC)

Answer 2

[antonwx]

Дай пользователю самому выбрать любимые эмодзи. Задолбали ваши алгоритмы, знающие лучше меня что мне надо.

Answer 3

[vdem]

А не лучше ли хранить их в одном поле в JSON? Например, сделать два поля - mostUsed (наиболее часто использующиеся) и recentlyUsed (последние использованные, чтобы на основании этих данных новые использующиеся эмодзи могли попасть в mostUsed). ИМХО для такой функциональности не стоит заводить отдельную таблицу.

P.S. Ну и конечно хранить количество (а лучше - частоту встречаемости) случаев использования, чтобы либо добавлять в mostUsed, либо убирать оттуда в пользу эмодзи из recentlyUsed.

Comments

[EchoStan]

Сам, пока писал, подумал в сторону того, что на шаге 3 (в коде приложения) мы имеем всю суть обновления и остаётся только обновить счётчик. Коллега того же мнения =)