Re: BUG #13817: Query planner strange choose while select/count small part of big table - complete

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