sql - PostgreSQL - 查找具有特定值的最旧记录

Question

我有一个文档管理系统，可以在历史表中记录所有历史事件。我被要求能够为给定日期的给定客户提供状态为 5 的最早的 doc_id。该表看起来像这样(为简单起见被截断):

doc_history:
    id integer
    doc_id integer
    event_date timestamp
    client_id integer
    status_id integer

client_id 和status_id 列是事件发生后文档的值。这意味着由 doc_id 定义的文档的最大历史事件行将匹配文档表中的相同列。按特定事件日期限制事件，您可以查看当时文档的值。因为这些值不是静态的，所以我不能简单地搜索 status_id 为 5 的特定 client_id，因为找到的结果可能与文档的 max(id) 不匹配。希望这是有道理的。

我发现可以工作但速度很慢的方法如下:

select
    t.*
from
    (select
        distinct on (doc_id),
        *
    from
        doc_history
    where
        event_date <= '2013-02-17 23:59:59'
    order by
        doc_id, id desc) t
where
    t.client_id = 9999 and
    t.status_id = 5
limit 1;

基本上，我在给定的最大事件日期之前获取特定文档 ID 的最大 ID，然后验证该最大历史记录项目是否已分配给给定的客户端，状态设置为 5。

我这样做的缺点是我正在扫描所有客户的所有历史记录以获得它们的最大值，然后找到我正在寻找的一个客户和状态。截至目前，这会扫描大约 1506 万行，在我的开发服务器上大约需要 90 秒(速度并不快)。

为了让事情变得更复杂，我需要为前一周的每一天执行此操作，或者每次运行总共七次。此外，系统中的所有文档都以状态 5 开头，代表新的。这样一来，此查询将只返回为该客户输入的第一个文档:

select * from doc_history where client_id = 9999 and
    status_id = 5 and
    event_date <= '2013-02-17 23:59:59'
    order by id limit 1;

我希望做的是扫描直到找到与特定客户端和状态值匹配的特定文档的最大历史记录，而不必首先为所有客户端找到所有文档 ID 的最大 ID。我不知道这是否可以通过窗口函数(分区依据)或我目前没有看到的其他一些逻辑来完成。

doc_history 表中事件之一的示例:

# select id, doc_id, event, old_value, new_value, event_date, client_id, status_id from doc_history where doc_id = 9999999 order by id;
    id    | doc_id  | event | old_value | new_value |         event_date         | client_id | status_id
----------+---------+-------+-----------+-----------+----------------------------+-----------+-----------
 25362415 | 9999999 |    13 |           |           | 2013-02-14 11:49:50.032824 |      9999 |         5
 25428192 | 9999999 |    15 |           |           | 2013-02-18 11:15:48.272542 |      9999 |         5
 25428193 | 9999999 |     7 | 5         | 1         | 2013-02-18 11:15:48.301377 |      9999 |         1

事件7是status changed，新旧值显示从5变成了1，体现在status_id列。对于小于或等于 2013-02-17 23:59:59 的 event_date，上述记录将是最旧的"new"文档，status_id 为 5，但在 2/17/2013 之后它不会有。

Answer 1

这应该多更快:

SELECT *
FROM   doc_history h1
WHERE  event_date < '2013-02-18 0:0'::timestamp
AND    client_id = 9999
AND    status_id = 5
AND NOT EXISTS (
   SELECT 1
   FROM   doc_history h2
   WHERE  h2.doc_id = h1.doc_id
   AND    h2.event_date < '2013-02-18 0:0'::timestamp
   AND    h2.event_date > h1.event_date  -- use event_date instead of id!
   )
ORDER  BY doc_id
LIMIT  1;

我很难理解您的描述。基本上，正如我现在所理解的那样，对于给定的 (client_id, status_id)，您希望具有最大 doc_id 的行与 event_date 之前给定时间戳，其中不存在具有相同 doc_id 的更高 id(等于稍后的 event_date)的其他行。

请注意我是如何替换您示例中的条件的:

WHERE  event_date <= '2013-02-17 23:59:59'

与:

WHERE  event_date < '2013-02-18 0:0'

因为你有小数秒，你的表达式会因为时间戳而失败，比如:
'2013-02-17 23:59:59.123'

我在 NOT EXISTS 半连接中使用 h2.event_date > h1.event_date 而不是 h2.id > h1.id 因为我认为假设更大的 id 等于以后的 event_date 是不明智的。您可能应该单独依赖 event_date。

为了加快速度，您需要一个 multicolumn index形式(更新):

CREATE INDEX doc_history_multi_idx
ON doc_history (client_id, status_id, doc_id, event_date DESC);

根据您的反馈，我改变了 doc_id, event_date DESC 的位置，这应该更好地适应 ORDER BY doc_id LIMIT 1。

如果条件 status_id = 5 是常量(你总是检查 5)，一个 partial index相 react 该更快，但是:

CREATE INDEX doc_history_multi_idx
ON doc_history (client_id, doc_id, event_date DESC)
WHERE status_id = 5;

和:

CREATE INDEX doc_history_id_idx ON doc_history (doc_id, event_date DESC);