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

Hi,

I've done it already. I've also already check rewrited query. No changes.
But i have a idea. There is generated base and there are "normal"
shipment_order_item with 3 - 100 maybe 10000 shipment_order_sub_item but
one has 3 992 102. Could it be a problem?

If yes that is my bad to ask You because I'belive there will not occur in
production and counting could bye match faster.

regards,
Marcin

Pozdrawiam,
Marcin

2015-12-17 16:00 GMT+01:00 Feike Steenbergen <feikesteenbergen(at)gmail(dot)com>:

> 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
>
>
>
>
>
>