3

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

  • 1
    Which DBMS product are you using? Query tuning depends a lot on the specific database product – a_horse_with_no_name Aug 20 '20 at 8:40
  • 1
    you have also to add a query plan and a CREATE table for all involved tables helps also – nbk Aug 20 '20 at 8:43
  • I use postgress, 1 min I will add CREATE table statements – Darii Petru Aug 20 '20 at 8:44
1

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.

  • sqlfiddle.com/#!17/92c8f7/4 this query retrun 2 result, but I need only one, from question: 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. – Darii Petru Aug 20 '20 at 11:59
  • Do you always expect at most 1 row? Would it be order=3 or order=5 in the above query? – Lennart Aug 20 '20 at 12:17
  • No, there are many orders and I expect actions that need to be treated – Darii Petru Aug 20 '20 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.