From: Richard Huxton <dev@archonet.com>
To: "Ключников А.С." <alexs@analytic.mv.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: query from partitions
Date: Tue, 13 Dec 2005 15:59:11 +0000
Message-ID: <439EEFCF.8000505@archonet.com> (raw)
In-Reply-To: <20051213151818.GA10021@mail.analytic.mv.ru>
References: <20051213151818.GA10021@mail.analytic.mv.ru>
Ключников А.С. 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, alexs@analytic.mv.ru
Subject: Re: query from partitions
In-Reply-To: <439EEFCF.8000505@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox