Subject: Re: query from partitions
From: Simon Riggs <simon@2ndquadrant.com>
To: Richard Huxton <dev@archonet.com>
Cc: =?iso-8859-5?Q?=22=BA=DB=EE=E7=DD=D8=DA=DE=D2_?=
	=?iso-8859-5?Q?=B0=2E=C1=2E=22?= <alexs@analytic.mv.ru>,
	pgsql-performance@postgresql.org
In-Reply-To: <439EEFCF.8000505@archonet.com>
References: <20051213151818.GA10021@mail.analytic.mv.ru>
	<439EEFCF.8000505@archonet.com>
Content-Type: text/plain; charset=utf-8
Date: Tue, 13 Dec 2005 22:48:02 +0000
Message-Id: <1134514082.27873.116.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

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