0

This is my victim query

SELECT *,
       lag(quantity)
       over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) -
       quantity as orders
FROM stocks
WHERE check_id in (SELECT check_id
                   FROM stocks_check_ids
                   WHERE product_id IN (123451249)
                   ORDER BY created_at DESC)

It takes up to 32 seconds execution time [QUERY PLAN]

This subquery

SELECT check_id
FROM stocks_check_ids
WHERE product_id IN (123451249)
ORDER BY created_at DESC

Returns 6 rows with less than 0.4ms [QUERY PLAN]

948cbe16-148f-46bf-900b-6775fdafaf6a
61f60a06-3e25-437f-b09f-5183a9f60076
eec338f9-3548-4f37-93fa-1b5e454ad08e
7506bd20-b5a6-403c-a460-91a425a2a038
4dbad9a9-3036-4204-baad-e1f4927487cb
2db3cffd-255a-48ab-b2d9-5f29d2592973

If i switch my subquery to these rows, like this

SELECT *,
       lag(quantity)
       over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) -
       quantity as orders
FROM stocks
WHERE check_id in ('948cbe16-148f-46bf-900b-6775fdafaf6a',
                   '61f60a06-3e25-437f-b09f-5183a9f60076',
                   'eec338f9-3548-4f37-93fa-1b5e454ad08e',
                   '7506bd20-b5a6-403c-a460-91a425a2a038',
                   '4dbad9a9-3036-4204-baad-e1f4927487cb',
                   '2db3cffd-255a-48ab-b2d9-5f29d2592973')

then it takes up to 68ms [QUERY PLAN]

What am i doing wrong?

32 seconds VS 68 milliseconds Why there is a such big difference in query plan?

2
  • 1
    Why do you have an order by in the subquery? Care to post the table and index definitions?
    – mustaccio
    Mar 13, 2023 at 17:55
  • @mustaccio, Yeah, maybe it is wrong, but even without order by... same results
    – glmn
    Mar 13, 2023 at 18:03

4 Answers 4

2

Example of join instead of IN

SELECT s.*,
   lag(quantity)
   over (partition by s.product_id, s.size_id, s.warehouse_id 
         order by s.size_id, s.warehouse_id, s.created_at ASC) -
   s.quantity as orders
FROM stocks s
JOIN stocks_check_ids x
    USING (check_id)
WHERE x.product_id IN (123451249)
1

if check_id is unique, then you can rewrite your query as Lennart suggested above

SELECT s.*,
   lag(quantity)
   over (partition by s.product_id, s.size_id, s.warehouse_id 
         order by s.size_id, s.warehouse_id, s.created_at ASC) -
   s.quantity as orders
FROM stocks s,stocks_check_ids c
WHERE x.product_id IN (123451249) and c.check_id = s.check_id

Create an index on stocks(check_id) : stocks_check_ids will serve as a derive table, and will be accessed using a index lookup with a local condition(x.product_id IN (123451249));

Create another index on stocks_check_ids(product_id): stocks will serve as a driven table, and will be accessed using a equal-ref index lookup with a ref condition(c.check_id = s.check_id).

0
0

As @mustaccio points out, you have an ORDER BY in the subquery, and this is defeating the optimizer. This sub-select is a great candidate to be "pulled up" into a join and optimized as join criteria or evaluated during selection. By providing a list, you have changed a scan into a quick equality check.

0

Without knowing the full table schema and number of rows in each table, here is an example of query using join. You need to update it for the partition as required

select prd.check_id, stk.*
from stock_check_ids chk 
left outer join stocks stk
on stk.check_id = chk.check_id
where chk.product_id = 123451249

this will fetch all rows from chk where the product_id matches and then fetch matching data from stk

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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