Re: query from partitions

Ключников А.С. 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...

-- 
   Richard Huxton
   Archonet Ltd