Hi,
When analysing your plain using explain.depesz.com, we can clearly see where
the reason lies the seq scan is preferred:
http://explain.depesz.com/s/Rus
This line clearly stands out on the "rows x" column:
(cost=1.24..126,838.50 rows=3,992,515 width=8) (actual time=1.242..1.265
rows=3 loops=1)
The optimizer expects 4 million rows to be returned, which would mean 4
million
index scans on using fk_fk_rr5k2n8n892ye3uposkh3xp6v_idx, yet in reality 3
rows
are returned.
That's quite a difference.
- Could you ANALYZE all tables involved and reissue the query?
Looking at your query, it seems a rewrite may help some,
how does the following perform and look like when explain analyzed?
SELECT *
FROM shipment_order_sub_item this_
-- Dropping LEFT JOIN, as we are later filtering on orderitem1_.id, which
-- would make this an INNER JOIN again
JOIN shipment_order_item orderitem1_ ON
(this_.shipment_order_item_id=orderitem1_.id)
JOIN shipment_order_item oi_ ON (orderitem1_.id=oi_.id)
JOIN shipment_order order_1 ON (oi_.order_id=order1_.id)
JOIN court_department courtdepar3_ ON
(order1_.court_department_id=courtdepar3_.department_id)
JOIN application_user user2_ ON (order1_.user_id=user2_.users_id)
WHERE order1_.id = 610
AND order1_.court_department_id in (1,292,32768 );
regards,
Feike
