查询优化

Question

我想从两个表(orders, actions)中优化两个查询，其关系为1到多

现在我从订单中做出一个选择：

SELECT "id"
FROM   "orders"
WHERE "orders"."status" = 'new' 

然后，对于我选择的每一个订单：

SELECT *
FROM   "actions" AS "actions" 
WHERE  "actions"."status" IN ( 'new', 'processing' ) 
       AND "actions"."order_id" = ${orderId} 
ORDER  BY CASE 
            WHEN ( "status" = 'processing' ) THEN 0 
            WHEN( "status" = 'new' ) THEN 1 
            ELSE 2 
          END ASC, 
          CASE 
            WHEN ( "direction" = 'out' ) THEN 0 
            WHEN( "direction" = 'in' ) THEN 1 
            ELSE 2 
          END ASC, 
          "actions"."id" ASC 
LIMIT  1; 

在代码中，我检查action.due_date <= now是否做了什么事情。

我试过：

SELECT o.status, ac.* FROM (
    SELECT *
    FROM   "actions" AS "actions" 
    WHERE  "actions"."status" IN ( 'new', 'processing' )
    ORDER  BY   CASE 
            WHEN ( "status" = 'processing' ) THEN 0 
            WHEN( "status" = 'new' ) THEN 1 
            ELSE 2 
          END ASC, 
          CASE 
            WHEN ( "direction" = 'out' ) THEN 0 
            WHEN( "direction" = 'in' ) THEN 1 
            ELSE 2 
          END ASC, 
          "actions"."id" ASC 
    LIMIT  1) AS ac
INNER JOIN orders o on o.id = ac.order_id
where ac.due_date <= ${now}
AND o.status = 'new'

以及：

SELECT * FROM orders as o
INNER JOIN (
    SELECT *
    FROM   "actions" AS "actions" 
    WHERE  "actions"."status" IN ( 'new', 'processing' )
    ORDER  BY   CASE 
            WHEN ( "status" = 'processing' ) THEN 0 
            WHEN( "status" = 'new' ) THEN 1 
            ELSE 2 
          END ASC, 
          CASE 
            WHEN ( "direction" = 'out' ) THEN 0 
            WHEN( "direction" = 'in' ) THEN 1 
            ELSE 2 
          END ASC, 
          "actions"."id" ASC 
    LIMIT  1) a on a.order_id = o.id AND a.due_date <= ${now}
where o.status = 'new' 

数据和所需答复的实例：

Order table
id  status
1   new
2   new

Actions table:
id  order_id     status      direction    due_date
1     1          success     out          now-1
2     1          processing  in           now+1
3     1          new         out          now-1
4     2          success     out          now-1
5     2          new         in           now-1
6     2          closed      out          now-1

响应：

id  order_id     status      direction    due_date
5   2            new         in           now-1

Order id 1不存在，因为操作id 2、due_date > now和其他操作需要等待，直到这个操作被处理。

Ps。操作表较小，我认为应该开始从选择操作中选择，以使选择更快/更优。

提前感谢

<#>编辑

添加木琴

Answer 1

这里有一个使用横向的想法：

SELECT o.status, ac.* 
FROM orders o 
CROSS JOIN LATERAL (
    SELECT *
    FROM   "actions" AS "actions" 
    WHERE  "actions"."status" IN ( 'new', 'processing' )
      AND  due_date <= ${now}
      AND  o.id = order_id    -- push join predicate inside sub-select
    ORDER  BY   CASE 
            WHEN ( "status" = 'processing' ) THEN 0 
            WHEN( "status" = 'new' ) THEN 1 
            ELSE 2 
          END ASC, 
          CASE 
            WHEN ( "direction" = 'out' ) THEN 0 
            WHEN( "direction" = 'in' ) THEN 1 
            ELSE 2 
          END ASC, 
          "actions"."id" ASC 
    LIMIT  1
) AS ac
WHERE o.status = 'new'

与我重新安排的查询相比，语义略有不同。