优化设置提高读性能

对于一个64 GB的服务器来说，你的内存设置似乎是合理的--除了work_mem = 512MB。这太高了。你的查询不是特别复杂，你的表也不是那么大。
4.5百万行（300 k x 15）在简单的连接表documents_tags中应该占用~ 156 MB，PK另占96 MB。对于您的查询，您通常不需要读取整个表，只需读取索引的一小部分。对于像您这样的 "大多为只读"，你应该看到***索引扫描***只在PK的索引上。你不需要那么多的work_mem-这 * 可能 * 没有多大关系-除非你有很多并发查询。引用手册：
...多个正在运行的会话可能同时执行这样的操作。因此，所使用的总内存可能是work_mem值的许多倍;在选择该值时必须记住这一事实。
将work_mem设置得过高实际上可能会降低性能：

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

我建议将work_mem减少到128 MB或更少，以避免可能的内存不足-除非您有其他需要更多内存的常见查询。
还有其他几个Angular 可以优化读取性能：

• Configuring PostgreSQL for read performance

关键问题：前导索引列

所有这些可能会有一点帮助。但关键问题是这样的：

PRIMARY KEY (document_id, tag_id)

300 k文档，2个要排除的标签。理想情况下，您的索引以tag_id作为*前导*列，document_id作为第二列。如果索引仅针对(tag_id)，则无法获得仅索引扫描。如果此查询是您唯一的用例，请按以下演示更改PK。
或者更好：如果需要，您可以在(tag_id, document_id)上创建一个额外的普通索引-并将documents_tags上的其他两个索引仅放在(tag_id)和(document_id)上。它们在两个多列索引上没有提供任何内容。剩余的2索引（与之前的3索引相反）更小，并且在各个方面都更上级。理由：

• Is a composite index also good for queries on the first field?
• Working of indexes in PostgreSQL

同时，我建议也使用新的PK CLUSTER表，所有这些都在一个事务中，可能在本地有一些额外的maintenance_work_mem：

BEGIN;
SET LOCAL maintenance_work_mem = '256MB';

ALTER TABLE documents_tags 
  DROP CONSTRAINT documents_tags_pkey
, ADD  PRIMARY KEY (tag_id, document_id);  -- tag_id first.

CLUSTER documents_tags USING documents_tags_pkey;

COMMIT;

别忘了：

VACUUM ANALYZE documents_tags;

查询

查询本身是普通的。以下是4种标准技术：

• 选择其他表中不存在的行

NOT IN是-引用我自己的话：
仅适用于没有NULL值的小集合
您的使用案例：所有涉及的列NOT NULL和您的排除项列表非常短。您的原始查询是一个热门竞争者。
NOT EXISTS和LEFT JOIN / IS NULL一直是热门的竞争者。在其他答案中都有建议。LEFT JOIN必须是实际的LEFT [OUTER] JOIN。
EXCEPT ALL最短，但通常没有那么快。

1. NOT IN

SELECT document_id
FROM   documents d
WHERE  document_id NOT IN (
   SELECT document_id  -- no need for column alias, only value is relevant
   FROM   documents_tags
   WHERE  tag_id IN (12345, 7654)
   );

2. NOT EXISTS

SELECT document_id
FROM   documents d
WHERE  NOT EXISTS (
   SELECT FROM documents_tags
   WHERE  document_id = d.document_id
   AND    tag_id IN (12345, 7654)
   );

3. LEFT JOIN / IS NULL

SELECT d.document_id
FROM   documents d
LEFT   JOIN documents_tags dt ON dt.document_id = d.document_id
                             AND dt.tag_id IN (12345, 7654)
WHERE  dt.document_id IS NULL;

4. EXCEPT ALL

SELECT document_id
FROM   documents
EXCEPT ALL               -- ALL keeps duplicate rows and makes it faster
SELECT document_id
FROM   documents_tags
WHERE  tag_id IN (12345, 7654);

基准测试

去验证我的理论。

测试设置

SET random_page_cost = 1.1;
SET work_mem = '128MB';

CREATE TABLE documents (
  document_id serial PRIMARY KEY
, title       text
);

-- CREATE TABLE tags ( ...  -- irrelevant for this query    

CREATE TABLE documents_tags (
  document_id int  REFERENCES documents
, tag_id      int  -- REFERENCES tags  -- irrelevant for test
 -- no PK yet, to test seq scan
 -- it's also faster to create the PK after filling the big table
);

INSERT INTO documents (title)
SELECT 'some dummy title ' || g
FROM   generate_series(1, 300000) g;

INSERT INTO documents_tags(document_id, tag_id)
SELECT i.*
FROM   documents d
CROSS  JOIN LATERAL (
   SELECT DISTINCT d.document_id, ceil(random() * 50000)::int
   FROM   generate_series (1,30)
   WHERE  random() > .5
   ) i;

ALTER TABLE documents_tags ADD PRIMARY KEY (document_id, tag_id);  -- current idx
    
VACUUM ANALYZE documents_tags;
VACUUM ANALYZE documents;

请注意，由于我填充表的方式，documents_tags中的行在物理上被document_id聚集-这可能也是您当前的情况。

2016-08-06使用Postgres 9.5进行原始测试

在我的旧笔记本电脑与4 GB RAM。
对4个查询中的每个查询运行3次测试，每次最好运行5次以排除缓存影响。

***测试1：***使用documents_tags_pkey。索引 * 和 * 行的物理顺序对于我们的查询来说是 * 坏 * 的。
***测试2：***按照建议在(tag_id, document_id)上重新创建PK。
***测试三：***CLUSTER在新PK上，EXPLAIN ANALYZE的执行时间（毫秒）：

time in ms   | Test 1 | Test 2 | Test 3
---------------+--------+--------+-------
1. NOT IN      | 654    |  **70**    |  71  -- winner!
2. NOT EXISTS  | 684    | 103    |  97
3. LEFT JOIN   | 685    |  98    |  99
4. EXCEPT ALL  | 833    | 255    | 250

结论

• 关键元素是右索引，前面是tag_id-用于涉及 fewtag_id和 manydocument_id的查询。

确切地说，document_id比tag_id有更多不同的document_id并不重要。这也可能是另一种情况。Btree索引基本上对任何顺序的列执行相同的操作。事实上，查询中最具选择性的 predicate 过滤tag_id。这在前导索引列上更快。

• 排除几个tag_id的获胜查询是您的原始查询**NOT IN**。
• NOT EXISTS和LEFT JOIN / IS NULL产生相同的查询计划。对于超过几十个ID，我希望这些可以更好地扩展...
• 在只读的情况下，你会看到***只索引扫描***，所以 table 中的行的物理顺序变得无关紧要。因此，test 3 没有带来更多的改进。
• 如果对表的写入发生了，而autovacuum无法跟上，您将看到**（位图）索引扫描**。物理集群对于这些很重要。

2023-04-11使用Postgres 15重新测试

在具有64 GB RAM的较新笔记本电脑上具有大量work_mem。
我跳过了“测试2”，并添加了“测试1+”。这与测试1相同，但增加了max_parallel_workers_per_gather，以便使用3个并行工作者而不是2个。

time in ms   | Test 1 | Test 1+ | Test 3
---------------+--------+---------+-------
1. NOT IN      | 170    | 154     |  42
2. NOT EXISTS  | 153    | 132     |  **35**  -- winner!
3. LEFT JOIN   | 154    | 135     |  **35**  -- same query plan
4. EXCEPT ALL  | 276    | 254     | 137

正如预期的那样，业绩普遍改善。
NOT IN无法跟上，因为它带来了一些固有的优化障碍。现在明显的赢家是NOT EXISTS或LEFT JOIN/NOT NULL，使用所有索引扫描。

“测试1”和“测试1+”看起来比原来更好。那些使用了2个和3个工人，所以把数字乘以那个系数就可以得到实际的系统负载。

使用外部连接，在连接上使用标记条件，仅保留未命中的连接以返回指定标记中 none 匹配的位置：

select d.id
from documents d
join documents_tags t on t.document_id = d.id
  and t.tag_id in (12345, 7654)
where t.document_id is null