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

The following bug has been logged on the website:

Bug reference:      13816
Logged by:          Marcin Sieńko
Email address:      sienkomarcin@gmail.com
PostgreSQL version: 9.4.5
Operating system:   Windows 7 x64
Description:

Hi,

I've check todo list but i can't find exact problem i'm reporting. It seems
like query planner fires not needed sequence scan by all rows in table when
only a few rows were picked. I will explain on example. I have 2 tables with
relation 1 - n. Index on foreign key exist. On n side there are a lot o rows
(about 4 000 000 in my db). When i select/count rows form n table
joined/subselected from 1-side than planner traverse every of 4 000 000 rows
although it has only 3 in "valid from where conditions". Execution takes
2191 ms on my pc. When i turn off sequence scan it takes 12 ms.

Below i include query and explain analyze output with sequence scan on and
off. Similar situation is without subquery (with joins).

Query:

select
    *
        --count(this_.id) as y0_
    from
--
        shipment_order_sub_item this_
    left outer join
        shipment_order_item orderitem1_
            on this_.shipment_order_item_id=orderitem1_.id
    where
        orderitem1_.id in (
            select
                oi_.id as y0_
            from
                shipment_order_item oi_
            inner join
                shipment_order order1_
                    on oi_.order_id=order1_.id
            inner join
                court_department courtdepar3_
                    on
order1_.court_department_id=courtdepar3_.department_id
            inner join
                application_user user2_
                    on order1_.user_id=user2_.users_id
            where
                order1_.id = 610
                and order1_.court_department_id in (1,292,32768 )
        )
        ;