Message-ID: <439EEFCF.8000505@archonet.com>
Date: Tue, 13 Dec 2005 15:59:11 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: =?UTF-8?B?ItCa0LvRjtGH0L3QuNC60L7QsiDQkC7QoS4i?= <alexs@analytic.mv.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: query from partitions
References: <20051213151818.GA10021@mail.analytic.mv.ru>
In-Reply-To: <20051213151818.GA10021@mail.analytic.mv.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

=D0=9A=D0=BB=D1=8E=D1=87=D0=BD=D0=B8=D0=BA=D0=BE=D0=B2 =D0=90.=D0=A1. wro=
te:
> And
> select * from base=20
> 	where id in (1,2) and datatime between '2005-05-15' and '2005-05-17';
> 10 seconds
>=20
> select * from base
> 	where id in (select id from device where id =3D 1 or id =3D 2) and
> 	datatime between '2005-05-15' and '2005-05-17';
> 10 minits
>=20
> 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=20
query ends up as ...where (id=3D1 or id=3D2)...

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=20
planning, but I don't think it will ever execute a subquery and then=20
re-plan the outer query based on those results. Of course, someone might =

pop up and tell me I'm wrong now...

--=20
   Richard Huxton
   Archonet Ltd