Postgresql – Query optimisation

optimizationpostgresqlsubquery

I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many

Now I make one select from orders:

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

Then for each order I select:

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 table contain due_date, with results from actions, in code, I check if action.due_date <= now do something

I tried:

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'

and:

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'

Ex of data and required response:

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

Response:

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

Order id 1 is not present because action id 2 due_date > now and other actions need to wait until this action will be treated.

Ps. Actions table is smaller, I think to start the select from actions for the selection to be faster / optimized

Thanks in advance

Edit

Added sqlfiddle

Best Answer

Here is one idea using LATERAL:

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'

The semantic is slightly different compared with your query that I rearranged.

Related Question