database - postgreSQL 嵌套查询执行缓慢

Question

我有这三个表:

1. 创建单词表(id整数，word文本，freq整数)；
2. 创建表格句子(id整数，句子文本)；
3. 创建表索引(wordId整数，sentenceId整数，position整数)；

索引是倒排索引，表示哪个词出现在哪个句子中。此外，我有一个来自表单词和句子的 id 索引。

此查询确定给定单词出现在哪些句子中并返回第一个匹配项:

select S.sentence from sentences S, words W, index I
where W.word = '#erhoehungen' and W.id = I.wordId and S.id = I.sentenceId
limit 1;

但是当我想检索两个单词一起出现的句子时，例如:

select S.sentence from sentences S, words W, index I
where W.word = '#dreikampf' and I.wordId = W.id and S.id = I.sentenceId and
S.id in (
    select S.id from sentences S, words W, index I
    where W.word = 'bruederle' and W.id = I.wordId and S.id = I.sentenceId
)
limit 1;

这个查询要慢得多。有什么技巧可以加快速度吗？以下是我到目前为止所做的事情:

• 将 shared_buffer 增加到 32MB
• 将 work_mem 增加到 15MB
• 对所有表进行分析
• 如前所述，在单词 id 和句子 id 上创建了索引

问候。

€编辑:

这里是解释分析查询语句的输出:http://pastebin.com/t2M5w4na

这三个create语句其实就是我原来的create语句。我是否应该将主键添加到表的句子和单词中并将它们作为索引中的外键引用？但是索引表应该用什么主键呢？ SentId 和 wordId 一起不是唯一的，即使我添加 pos 表示单词在句子中的位置，它也不是唯一的。

更新为:

1. 创建单词表(id整数，单词文本，频率整数，主键(id))；
2. 建表句(id整型，句子正文，主键(id))；
3. 创建表索引(wordId整数，sentenceId整数，位置整数，外键(wordId)引用单词(id)，外键(sentenceId)引用句子(sentenceId));

Answer 1

我想这应该更有效率:

SELECT s.id, s.sentence FROM words w
JOIN INDEX i ON w.id = i.wordId
JOIN sentences s ON i.sentenceId = s.id
WHERE w.word IN ('#dreikampf', 'bruederle')
GROUP BY s.id, s.sentence
HAVING COUNT(*) >= 2

只需确保 IN 子句中的项目数量与 HAVING 子句中的项目数量相匹配。

fiddle here .