我有一个文档管理系统,记录了所有历史事件在一个历史表中。我被要求能够提供特定日期下给定客户端的状态为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日之后,它不再是。
date
vs.timestamp
: 嗯,'2013-02-18'::timestamp
也可以用,但是它看起来像是一个date
,而'2013-02-18 0:0'::timestamp
明确表示它是一个时间戳,即使你没有明确地添加类型转换。 - Erwin Brandstetterid
vs.event_date
: 你 知道它们是同步的,但系统不知道。这会影响索引等其他事项。正如你所看到的,我在两个索引中都使用了event_date
,因此您需要在查询中使用event_date
来使用它们。或者将所有内容切换到id
。像你所做的那样 混合使用 没有好处,但可能会带来一些潜在的缺点。如果可以保证id
的交付,那么它会稍微快一些。对于两个索引,integer
的 4 字节与timestamp
的 8 字节恰好落在 MAXALIGN 内。 - Erwin Brandstetter