Обсуждение: BUG #13816: Query planner strange choose while select/count small part of big table
BUG #13816: Query planner strange choose while select/count small part of big table
От
sienkomarcin@gmail.com
Дата:
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 ) ) ;