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是状态更改，旧值和新值显示它从5更改为1，这反映在status_id列中。对于小于或等于2013-02-17 23:59:59的event_date，上述记录将是具有状态ID为5的最旧的“NEW”文档，但在2013年2月17日之后，它不再是。

- Adam

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)和在给定时间戳之前的event_date，具有最大doc_id的行，其中不存在具有更高id（即更晚的event_date）的相同doc_id的其他行。

请注意，我替换了您示例中的条件：

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。

为了加快速度，你需要一个更新后形式为（多列索引）的多列索引：

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），则使用部分索引会更快，但是：

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);

- Erwin Brandstetter

---

如果我表达不清楚，我向您道歉。也许是因为我现在感冒了。我喜欢你提到的关于小数秒的观点，所以我会努力将其纳入其中（尽管这对于该系统来说是真正的边缘情况）。无论如何，我真的很喜欢你在这里提出的解决方案，明天我会进行测试。由于表格只能插入，而不能更新/删除，因此ID列是安全的，并且可以与事件日期互换，但是您的担忧是有道理的。关于您的时间戳的一个问题：是否有理由使用0:0而不是省略它？ - Adam

@Adam: date vs. timestamp: 嗯，'2013-02-18'::timestamp 也可以用，但是它看起来像是一个 date，而 '2013-02-18 0:0'::timestamp 明确表示它是一个时间戳，即使你没有明确地添加类型转换。 - Erwin Brandstetter

@Adam: id vs. event_date: 你 知道它们是同步的，但系统不知道。这会影响索引等其他事项。正如你所看到的，我在两个索引中都使用了 event_date，因此您需要在查询中使用 event_date 来使用它们。或者将所有内容切换到 id。像你所做的那样 混合使用 没有好处，但可能会带来一些潜在的缺点。如果可以保证 id 的交付，那么它会稍微快一些。对于两个索引，integer 的 4 字节与 timestamp 的 8 字节恰好落在 MAXALIGN 内。 - Erwin Brandstetter

我今天一直很忙，但我可以说你的查询确实有效且更快。然而，你在这里建议的多索引在我的9.2服务器中没有使用。相反，它选择使用我的简单doc_id索引。不过，doc_id、event_date desc索引正在被使用。我尝试删除所有索引，只使用你的索引进行测试以确保。在dev上，使用完整表格安装，运行时间约为60秒，而我的则需要90多秒。我也同意你关于event_date与id的看法，并已相应地调整了我的代码。 - Adam

@Adam：在收到您的反馈并进一步考虑后，我更新了有关索引部分。 - Erwin Brandstetter

Answer 2

提供给定客户和日期的状态为5的最早doc_id

这样做就可以了：

select
    min(doc_id) doc_id
from
    doc_history
where
    client_id = 9999
    and status_id = 5
    and date event_date = '2013-02-17'

我已经阅读了您的问题多次，但无法理解您在说什么。

- Clodoaldo Neto

---

我要做的是：在2013年2月17日客户X的最旧未处理（NEW）文档是什么。如果我从历史记录表中获取最早的事件，我会得到一个新文档，但我需要在2013年2月17日之前客户X的状态为5（新）的最新事件。状态和客户值在事件之间不是静态的，因为它们反映了事件引起的变化（例如状态和客户的更改）。 - Adam

我已经添加了一个示例，展示表格中实际行的样子。 - Adam

Answer 3

如果我理解正确，你的一个等价且可能更快的查询是：

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

- MatheusOl

---

很遗憾，它并不是完美的。这是一个不错的尝试，但是不能保证这个查询找到的事件是event_date最大值所记录的最后一个事件。例如，在此处返回一个事件后，可能会有一个状态ID或客户ID不同的事件。在上面的示例文档记录中，它在2/14至2/18期间具有状态ID 5。如果您的查询使用event_date <= 2013-02-18 23:59:59，则最终会得到行ID 25428192，但这是无效的，因为截至2/18，它的状态ID为1，而不是由行25428193表示的5。 - Adam