Query optimization advice for beginners

Hello mail group members,

I started a new job as PostgreSQL DBA. This is my first mail, I hope the mail I sent meets the rules.

There is a query that runs slowly when I look at the logs of the database. When I check the resources of the system, there is no problem in the resources, but this query running slowly. There is no "Seq Scan" in the queries, so the tables are already indexed. But I did not fully understand if the indexes were made correctly. When I analyze the query result on explain.depesz, it seems that the query is taking too long.

How should I fix the query below? How should I read the output of explain.depesz?

Thank you in advance for your help.

select pro.id as pro_id
, pro.code
, coalesce(s.is_pick, false)
, coalesce(sum(sb.quantity), 0) as pick_quantity
from mainproduct_productmetaproduction pro, order_basketitemdetail bid
left join shelf_shelvedproductbatch sb on sb.basketitem_detail_id = bid.id
left join shelf_shelvedproducts sp on sp.id = sb.shelved_product_id
left join shelf_shelf s on s.id = sp.shelf_id
where pro.id = bid.production_id
and (
select coalesce(sum(bid.quantity), 0)
from order_basketitem bi
, order_basketitemdetail bid
, order_order o
where o.type in (2,7,9) and o.id = bi.order_id
and o.is_cancelled = false
and bi.is_cancelled = false
and o.is_closed = false
and o.is_picked = false
and o.is_invoiced = false
and o.is_sent = false
and bi.id = bid.basketitem_id
and bid.quantity > (
select coalesce(sum(picked_quantity),0)
from order_basketitembatch bib
where bib.detail_id=bid.id
)
and bid.code = pro.code
) > 0
group by 1,2,3 --,bid.pallet_item_quantity
having coalesce(s.is_pick, false)
and round((coalesce(sum(sb.quantity), 0) / GREATEST(MAX(bid.pallet_item_quantity), 1)::float)::numeric, 2) <= 0.15

https://explain.depesz.com/s/G4vq

Yours truly,
Kemal Ortanca