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

The following bug has been logged on the website:

Bug reference: 13817
Logged by: Marcin_S
Email address: sienkomarcin(at)gmail(dot)com
PostgreSQL version: 9.4.5
Operating system: Windows 7 x64
Description:

Hi,

First sorry for not completed last bug (browser handle it too fast :). Here
is complete version:

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

sequence scan on (default):

"Hash Join (cost=12.88..108087.13 rows=3992515 width=177) (actual
time=2426.511..2426.515 rows=3 loops=1)"
" Hash Cond: (this_.shipment_order_item_id = orderitem1_.id)"
" -> Seq Scan on shipment_order_sub_item this_ (cost=0.00..90031.15
rows=3992515 width=125) (actual time=0.022..1071.889 rows=3992110 loops=1)"
" -> Hash (cost=12.87..12.87 rows=1 width=60) (actual time=0.175..0.175
rows=3 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Hash Semi Join (cost=11.69..12.87 rows=1 width=60) (actual
time=0.166..0.173 rows=3 loops=1)"
" Hash Cond: (orderitem1_.id = oi_.id)"
" -> Seq Scan on shipment_order_item orderitem1_
(cost=0.00..1.13 rows=13 width=52) (actual time=0.009..0.010 rows=13
loops=1)"
" -> Hash (cost=11.68..11.68 rows=1 width=8) (actual
time=0.144..0.144 rows=3 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Nested Loop (cost=0.28..11.68 rows=1 width=8)
(actual time=0.079..0.139 rows=3 loops=1)"
" Join Filter: (order1_.user_id =
user2_.users_id)"
" Rows Removed by Join Filter: 9"
" -> Nested Loop (cost=0.28..10.59 rows=1
width=16) (actual time=0.063..0.105 rows=3 loops=1)"
" -> Nested Loop (cost=0.00..2.29 rows=1
width=24) (actual time=0.027..0.052 rows=3 loops=1)"
" -> Seq Scan on shipment_order_item
oi_ (cost=0.00..1.16 rows=1 width=16) (actual time=0.007..0.010 rows=3
loops=1)"
" Filter: (order_id = 610)"
" Rows Removed by Filter: 10"
" -> Seq Scan on shipment_order
order1_ (cost=0.00..1.11 rows=1 width=24) (actual time=0.006..0.007 rows=1
loops=3)"
" Filter: ((id = 610) AND
(court_department_id = ANY ('{1,292,32768}'::bigint[])))"
" Rows Removed by Filter: 6"
" -> Index Only Scan using
court_department_pkey on court_department courtdepar3_ (cost=0.28..8.29
rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=3)"
" Index Cond: (department_id =
order1_.court_department_id)"
" Heap Fetches: 3"
" -> Seq Scan on application_user user2_
(cost=0.00..1.04 rows=4 width=8) (actual time=0.003..0.004 rows=4 loops=3)"
"Planning time: 1.765 ms"
"Execution time: 2426.724 ms"

sequence scan off:

"Nested Loop (cost=1.24..138607.34 rows=3992515 width=177) (actual
time=0.151..0.168 rows=3 loops=1)"
" -> Nested Loop Semi Join (cost=0.81..45.31 rows=1 width=60) (actual
time=0.139..0.143 rows=3 loops=1)"
" Join Filter: (orderitem1_.id = oi_.id)"
" Rows Removed by Join Filter: 33"
" -> Index Scan using shipment_order_item_pkey on
shipment_order_item orderitem1_ (cost=0.14..12.33 rows=13 width=52) (actual
time=0.018..0.023 rows=13 loops=1)"
" -> Materialize (cost=0.67..32.79 rows=1 width=8) (actual
time=0.004..0.007 rows=3 loops=13)"
" -> Nested Loop (cost=0.67..32.78 rows=1 width=8) (actual
time=0.049..0.086 rows=3 loops=1)"
" -> Nested Loop (cost=0.54..24.62 rows=1 width=16)
(actual time=0.042..0.069 rows=3 loops=1)"
" -> Nested Loop (cost=0.27..16.32 rows=1
width=24) (actual time=0.028..0.044 rows=3 loops=1)"
" -> Index Scan using
fk_fk_mt3v1s9gl7rr0lo83il8gy00d_idx on shipment_order_item oi_
(cost=0.14..8.15 rows=1 width=16) (actual time=0.014..0.017 rows=3
loops=1)"
" Index Cond: (order_id = 610)"
" -> Index Scan using shipment_order_pkey on
shipment_order order1_ (cost=0.13..8.15 rows=1 width=24) (actual
time=0.006..0.007 rows=1 loops=3)"
" Index Cond: (id = 610)"
" Filter: (court_department_id = ANY
('{1,292,32768}'::bigint[]))"
" -> Index Only Scan using court_department_pkey
on court_department courtdepar3_ (cost=0.28..8.29 rows=1 width=8) (actual
time=0.005..0.006 rows=1 loops=3)"
" Index Cond: (department_id =
order1_.court_department_id)"
" Heap Fetches: 3"
" -> Index Only Scan using application_user_pkey on
application_user user2_ (cost=0.13..8.15 rows=1 width=8) (actual
time=0.003..0.004 rows=1 loops=3)"
" Index Cond: (users_id = order1_.user_id)"
" Heap Fetches: 3"
" -> Index Scan using fk_fk_rr5k2n8n892ye3uposkh3xp6v_idx on
shipment_order_sub_item this_ (cost=0.43..98636.88 rows=3992515 width=125)
(actual time=0.006..0.006 rows=1 loops=3)"
" Index Cond: (shipment_order_item_id = orderitem1_.id)"
"Planning time: 1.552 ms"
"Execution time: 0.311 ms"