postgresql - 优化同一个表的两个记录之间的连接

Question

我正在尝试搜索数据库并在同一个表中找到两条记录，这些记录被插入到一个特定的事件中。这将告诉我必须在系统中的哪些地方进行一些优化。

我尝试了很多查询，最后一个给了我正确的结果:

SELECT woh.*
FROM workorderhistory woh 
WHERE woh.operation = 'REQ_CONVER' AND woh.operationtime > (
    SELECT MAX(woh2.operationtime) FROM workorderhistory woh2 WHERE woh2.operation = 'CLOSE' 
) AND woh.workorderid = woh2.workorderid
ORDER BY woh.workorderid DESC
LIMIT 100

然而，这一个花了 1.5 小时，在 16000 条记录上......所以这对我来说绝对不行......

查询中最大的问题是我只需要将我的条件与最新的closed 记录相匹配。

数据会说明更多:

在示例一中，查询很简单:

SELECT woh.*
FROM workorderhistory woh 
INNER JOIN workorderhistory woh2
ON woh.workorderid = woh2.workorderid
WHERE woh.operation = 'REQ_CONVER' AND woh2.operation = 'CLOSE' AND woh.operationtime > woh2.operationtime

但是，我需要先检索最后关闭的记录。这是我需要的对比记录。

我尝试使用带子查询的 INNER JOIN 来执行此操作:

SELECT woh.*, woh2.operationtime AS woh2operationtime, woh2.operationtime AS woh2operationtime
FROM workorderhistory woh 
INNER JOIN (SELECT workorderid, operationtime FROM workorderhistory WHERE operation = 'CLOSE' ORDER BY historyid DESC LIMIT 1) woh2
ON woh.workorderid = woh2.workorderid AND woh2.operationtime < woh.operationtime
WHERE woh.operation = 'REQ_CONVER'
ORDER BY woh.workorderid ASC
LIMIT 100

但是并没有返回正确的结果，还是使用了第一个CLOSE记录。

知道如何为 PostgresSQL 优化这个(我觉得 super 简单的)查询吗？

Answer 1

希望我没看错你的问题。

SELECT woh.*
FROM workorderhistory woh
join (select max(operationtime) as operationtime , workorderid 
      from workorderhistory 
      where operation = 'CLOSE' group by workorderid 
     ) woh2 
on woh.workorderid = woh2.workorderid
where woh.operationtime > woh2.operationtime
   and operation = 'REQ_CONVER'

列 workorderid 上的索引将提高性能。