sql - 如何在 PostgreSQL 中存储和查询同一文档的版本？

Question

我正在 PostgreSQL 9.4 中存储文档的版本。每次用户创建新版本时，它都会插入一行，以便我可以跟踪所有随时间变化的变化。每行与前面的行共享一个 reference_id 列。有些行获得批准，有些仍保留为草稿。每行还有一个 viewable_at 时间。

id | reference_id | approved | viewable_at         | created_on | content
1  | 1            | true     | 2015-07-15 00:00:00 | 2015-07-13 | Hello
2  | 1            | true     | 2015-07-15 11:00:00 | 2015-07-14 | Guten Tag
3  | 1            | false    | 2015-07-15 17:00:00 | 2015-07-15 | Grüß Gott

最常见的查询是获取按reference_id 分组的行，其中approved 为true 且viewable_at 小于当前时间。 (在这种情况下，行 id 2 将包含在结果中)

到目前为止，这是我想出的最好的查询，它不需要我添加额外的列:

SELECT DISTINCT ON (reference_id) reference_id, id, approved, viewable_at, content 
FROM documents 
WHERE approved = true AND viewable_at <= '2015-07-15 13:00:00' 
ORDER BY reference_id, created_at DESC`

我在 reference_id 上有一个索引，在 approved 和 viewable_at 上有一个多列索引。

在我的本地机器上，只有 15,000 行，它仍然平均需要几百毫秒 (140 - 200)。我怀疑不同的调用或排序可能会减慢速度。

存储此信息的最有效方法是什么，以便 SELECT 查询的性能最高？

EXPLAIN (BUFFERS, ANALYZE) 的结果:

                                                              QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
Unique  (cost=6668.86..6730.36 rows=144 width=541) (actual time=89.862..99.613 rows=145 loops=1)
  Buffers: shared hit=2651, temp read=938 written=938
  ->  Sort  (cost=6668.86..6699.61 rows=12300 width=541) (actual time=89.861..97.796 rows=13184 loops=1)
        Sort Key: reference_id, created_at
        Sort Method: external merge  Disk: 7488kB
        Buffers: shared hit=2651, temp read=938 written=938
        ->  Seq Scan on documents  (cost=0.00..2847.80 rows=12300 width=541) (actual time=0.049..40.579 rows=13184 loops=1)
              Filter: (approved AND (viewable_at < '2015-07-20 06:46:55.222798'::timestamp without time zone))
              Rows Removed by Filter: 2560
              Buffers: shared hit=2651
Planning time: 0.218 ms
Execution time: 178.583 ms
(12 rows)

文档使用说明:

文档是手动编辑的，我们还没有每隔 X 秒自动保存文档或其他任何东西，因此数量会相当低。此时，每个 reference_id 平均有 7 个版本，平均只有 2 个批准版本。 (~30%)

在最小和最大方面，绝大多数文档将有 1 或 2 个版本，似乎任何文档都不会超过 30 或 40。有一个垃圾收集过程可以清除早于 a 的未批准版本一周，因此版本总数应该保持在相当低的水平。

为了检索和实际使用，我可以在查询中使用限制/偏移量，但在我的测试中这并没有太大的不同。理想情况下，这是一个填充 View 或其他内容的基本查询，以便我可以在这些结果之上执行其他查询，但我不完全确定这将如何影响结果性能并且愿意接受建议。我的印象是，如果我能让这个存储/查询尽可能简单/快速，那么从这一点开始的所有其他查询都可以得到改进，但很可能我错了，每个查询都需要更多的独立思考。

Answer 1

查看您的解释输出，您似乎正在获取 documents 表中的大部分内容，因此它明智地进行了顺序扫描。您的行数估计是合理的，这里似乎没有任何统计问题。

它在磁盘上执行外部合并排序，因此您可能会看到通过增加 session 中的 work_mem 显着提高性能，例如

SET work_mem = '12MB'

(reference_id ASC, created_at DESC) WHERE (approved) 上的索引可能会有用，因为它将允许按要求的顺序获取结果。

您还可以尝试将 viewable_at 添加到索引中。我认为它可能必须是最后一列，但我不确定。或者甚至通过附加 viewable_at, id, content 并从结果集中省略不必要的 approved 列使其成为覆盖索引。这可能允许仅索引扫描，但我不确定是否涉及 DISTINCT ON。