here’s my query :
`select
sum(dnpi_qty * ((so_item_qty – so_item_delivered_qty) / so_item_qty))
from

                            (select stock_qty as dnpi_qty, qty as so_item_qty,
                                    delivered_qty as so_item_delivered_qty, so_item.parent, so_item.name
                            from `tabSales Order Item` so_item
                            where item_code = 'BA-XXX' and warehouse = 'W1'
                            and coalesce(so_item.delivered_by_supplier,0)=0
                            and so_item.qty > so_item.delivered_qty
                            and exists (select name from `tabSales Order` so where so.name=so_item.parent and
                                    so.status in ('On Hold','To Deliver','To Deliver and Bill')
                                    and so.docstatus = 1 and so.per_delivered < 100 order by so.modified desc
                            ) order by so_item.modified desc
                    ) tab
            where
                    so_item_qty > so_item_delivered_qty;`

query time is vary by 10-20 sec, rows return is 5k+ records

Explained query:
enter image description here
enter image description here

Table indexes:
table indexes

I’ve tried to use join instead of subquery but the result was similar

Please any suggestions to improve this query?