Window function with subquery results to unexpectedly slow query plan

Question

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?

Answer 1

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)

Answer 2

if check_id is unique, then you can rewrite your query to

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

And create an index on stocks(check_id) and an index on stocks_check_ids(product_id).

Answer 3

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.

Answer 4

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