On Tue, 2005-12-13 at 15:59 +0000, Richard Huxton wrote: > Ключников А.С. wrote: > > And > > select * from base > > where id in (1,2) and datatime between '2005-05-15' and '2005-05-17'; > > 10 seconds > > > > select * from base > > where id in (select id from device where id = 1 or id = 2) and > > datatime between '2005-05-15' and '2005-05-17'; > > 10 minits > > > > Why? > > Run EXPLAIN ANALYSE on both queries to see how the plan has changed. > > My guess for why the plans are different is that in the first case your > query ends up as ...where (id=1 or id=2)... > > In the second case, the planner doesn't know what it's going to get back > from the subquery until it's executed it, so can't tell it just needs to > scan base_1,base_2. Result: you'll scan all child tables of base. > > I think the planner will occasionally evaluate constants before > planning, but I don't think it will ever execute a subquery and then > re-plan the outer query based on those results. Of course, someone might > pop up and tell me I'm wrong now... Thats right. Partitioning doesn't work for joins in 8.1. Best Regards, Simon Riggs
