pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Richard Huxton <dev@archonet.com>
Cc: "Ключников А.С." <alexs@analytic.mv.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: query from partitions
Date: Tue, 13 Dec 2005 22:48:02 +0000
Message-ID: <1134514082.27873.116.camel@localhost.localdomain> (raw)
In-Reply-To: <439EEFCF.8000505@archonet.com>
References: <20051213151818.GA10021@mail.analytic.mv.ru>
	<439EEFCF.8000505@archonet.com>

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




view thread (5+ messages)

Message-ID: <1134514082.27873.116.camel@localhost.localdomain>
Permalink:  ../1134514082.27873.116.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1134514082.27873.116.camel@localhost.localdomain

 · 

reply
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: simon@2ndquadrant.com, dev@archonet.com, alexs@analytic.mv.ru
  Subject: Re: query from partitions
  In-Reply-To: <1134514082.27873.116.camel@localhost.localdomain>

* 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