sql - 从输入数组生成行

Question

假设我有一个表，其中包含许多名为 comments 的记录，并且每条记录仅包含一个文本正文:

CREATE TABLE comments(id INT NOT NULL, body TEXT NOT NULL, PRIMARY KEY(id));
INSERT INTO comments VALUES (generate_series(1,100), md5(random()::text));

现在，我有一个包含 N 个任意长度子字符串的输入数组。例如:

abc
xyzw
123456
not_found

对于每个输入值，我想返回符合特定条件的所有行。

例如，假设该表包含以下记录:

| id | body        |
| -- | ----------- |
| 11 | abcd1234567 |
| 22 | unkown12    |
| 33 | abxyzw      |
| 44 | 12345abc    |
| 55 | found       |

我需要一个返回以下结果的查询:

| substring | comments.id | comments.body |
| --------- | ----------- | ------------- |
| abc       | 11          | abcd1234567   |
| abc       | 44          | 12345abc      |
| xyzw      | 33          | abxyzw        |
| 123456    | 11          | abcd1234567   |

到目前为止，我有这个 SQL 查询:

SELECT substrings, comments.id, comments.body
FROM unnest(ARRAY[
  'abc',
  'xyzw',
  '123456',
  'not_found'
]) AS substrings
JOIN comments ON comments.id IN (
  SELECT id
  FROM comments as inner_comments
  WHERE inner_comments.body LIKE ('%' || substrings || '%')
);

但是数据库客户端卡了10多分钟。我是否遗漏了有关连接的信息？

请注意，这是我的问题的简化示例。我目前检查评论的不是LIKE语句，而是复杂的不同功能的switch-case语句(模糊匹配)。

Answer 1

IN 的绕行是不必要的，除非优化器可以重写它，但它很可能不能，否则会增加开销。尝试如果没有它会变得更好。

SELECT un.substring,
       comments.id,
       comments.body
       FROM unnest(ARRAY['abc',
                         'xyzw',
                         '123456',
                         'not_found']) un (substring)
       INNER JOIN comments
                  ON comments.body LIKE ('%' || un.substring || '%');

但是由于开头的通配符，这里仍然不能使用索引。您可能想看看 Full Text Search，看看您有哪些选择可以改善这种情况。