Re: query from partitions

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