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

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