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