LIKE搜索加入和连接记录是非常慢的（PostgreSQL）

Question

我从users表返回id的唯一列表，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%' 

UPDATE

我喜欢这个移动到一个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...; 

EDIT2：新的细节显示设置的原始问题的新的要求。因此，添加更新问题的新示例：

由于您使用OR条件对2个不同的表（位置和上传）进行查找，因此简单的JOIN不起作用。 但是这两种查找都是验证类型查找 - 只有看起来确实存在%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部分中的任何位置。因此，无论您是否使用错误的命名来切入您的示例真实查询，或者您使其他方式错别字是您需要验证并相应地调整我的示例查询。