Как поиск объединенных и сцепленных записей очень медленно (PostgreSQL)

Question

Я возвращаю уникальный список idиз users Таблица, where конкретные столбцы в связанной таблице (positions) содержит соответствующую строку.

Связанная таблица может иметь несколько записей для каждой записи пользователя.

Запрос занимает действительно очень много времени (он не масштабируется), поэтому мне интересно, правильно ли я структурирую запрос каким-либо фундаментальным образом?

Таблица пользователей:

id | name
-----------
1  | frank
2  | kim
3  | jane

Таблица положений:

id | user_id | title     | company | description
--------------------------------------------------
1  | 1       | manager   | apple   | 'Managed a team of...'
2  | 1       | assistant | apple   | 'Assisted the...'
3  | 2       | developer | huawei  | 'Build a feature that...'

Например: я хочу вернуть пользователя id если связанный positions запись содержит "яблоко" либо в title, company или же description колонны.

Запрос:

select
  distinct on (users.id) users.id,
  users.name,
  ...
from users
where (
    select
        string_agg(distinct users.description, ', ') ||
        string_agg(distinct users.title, ', ') ||
        string_agg(distinct users.company, ', ')
    from positions
    where positions.users_id::int = users.id
    group by positions.users_id::int) like '%apple%'

ОБНОВИТЬ

Мне нравится идея перенести это в join пункт. Но то, что я хочу сделать, - это фильтровать пользователей по условию ниже. И я не уверен, как это сделать в join,

1) найти ключевое слово в названии, компании, описании

or

2) поиск ключевого слова с полнотекстовым поиском в соответствующей строковой версии документа в другой таблице.

select
    to_tsvector(string_agg(distinct documents.content, ', '))
from documents
where users.id = documents.user_id
group by documents.user_id) @@ to_tsquery('apple')

Поэтому я изначально думал, что это может выглядеть так,

select
  distinct on (users.id) users.id,
  users.name,
  ...
from users
where (
    (select
        string_agg(distinct users.description, ', ') ||
        string_agg(distinct users.title, ', ') ||
        string_agg(distinct users.company, ', ')
    from positions
    where positions.users_id::int = users.id
    group by positions.users_id::int) like '%apple%')
    or
    (select
        to_tsvector(string_agg(distinct documents.content, ', '))
    from documents
    where users.id = documents.user_id
    group by documents.user_id) @@ to_tsquery('apple'))

Но тогда это было действительно медленно - я могу подтвердить медлительность из первого условия, а не полнотекстового поиска.

Answer 1

Возможно, это не лучшее решение, но быстрый вариант:

SELECT  DISTINCT ON ( u.id ) u.id,
        u.name
FROM    users u
JOIN    positions p ON (
                 p.user_id = u.id
            AND  ( description || title || company )
            LIKE '%apple%'
        );

В основном избавился от подзапроса, ненужного использования string_agg, группировки по таблице позиций и т. Д.

То, что он делает, делает условное соединение и удаление дубликатов покрывается distinct on,

PS! Я использовал псевдонимы таблиц u а также p сократить пример

РЕДАКТИРОВАТЬ: добавив также пример ГДЕ в соответствии с просьбой

SELECT  DISTINCT ON ( u.id ) u.id,
        u.name
FROM    users u
JOIN    positions p ON ( p.user_id = u.id )
WHERE   ( p.description || p.title || p.company ) LIKE '%apple%'
OR      ...your other conditions...;

РЕДАКТИРОВАТЬ 2: новые детали выявили новые требования оригинального вопроса. Итак, добавление нового примера для обновленного Ask:

Так как вы выполняете поиск в 2 разных таблицах (позиции и загрузки) с условием ИЛИ, тогда простое СОЕДИНЕНИЕ не будет работать. Но оба поиска являются поисками типа проверки - только поиск делает %apple% существует, то вам не нужно агрегировать и группировать и преобразовывать данные. С помощью EXISTS это возвращает TRUE Первый найденный матч - это то, что вам, похоже, нужно. Таким образом, удалив все ненужные части и используя с LIMIT 1 вернуть положительное значение, если первое совпадение найдено, и NULL, если нет (последнее сделает EXISTS становиться FALSE) даст вам тот же результат.

Итак, вот как вы могли бы решить это:

SELECT  DISTINCT ON ( u.id ) u.id,
        u.name
FROM    users u
WHERE   EXISTS (
            SELECT  1
            FROM    positions p
            WHERE   p.users_id = u.id::int
            AND     ( description || title || company ) LIKE '%apple%'
            LIMIT   1
        )
OR      EXISTS (
            SELECT  1
            FROM    uploads up
            WHERE   up.user_id = u.id::int -- you had here reference to table 'document', but it doesn't exists in your example query, so I just added relation to 'upoads' table as you have in FROM, assuming 'content' column exists there
            AND     up.content LIKE '%apple%'
            LIMIT   1
        );

NB! в вашем примере запросы имеют ссылки на таблицы / псевдонимы, такие как documents который нигде не отражается в FROM часть. Таким образом, либо вы сократили примерный запрос с неправильным наименованием, либо вы выбрали другой способ опечатки - это то, что вам необходимо проверить и соответствующим образом изменить в моем примере запроса.