pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vadim Mikheev <vadim@krs.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Patrick Giagnocavo <a222@redrose.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] qurey plan and indices
Date: Fri, 06 Aug 1999 10:09:08 +0800
Message-ID: <37AA43C4.5DDBE65F@krs.ru> (raw)
References: <4175.933863966@sss.pgh.pa.us>

Tom Lane wrote:
> 
> Yes.  The inner query must happen by sequential scan.  But the outer
> query can use an index if it is in the form
>         ... where (name = 'a') or (name = 'b') or (name = 'c');
> which is what "where name in ('a','b','c')" gets translated to.
> 
> However, the *real* problem here is that the inner query is treated
> as a subplan, which means it is re-evaluated for each tuple scanned
> by the outer query.  This is not very bright; the system ought to
> notice that the inner query does not depend on the state of the
> outer query, and do it only once.  There is already a notion of
> InitPlan vs. SubPlan.  I'm not sure why this case is being classified
> as a SubPlan, but it sure looks like it ought to be done as an
> InitPlan...

Not in all cases. First, to use InitPlan you would have to add 
DISTINCT to subquery to avoid duplicates. Second, there is another
way of optimization - cache/hash subquery results to avoid 
re-execution of subquery plan. Planner should make decision what
way to follow. 

Vadim



view thread (7+ messages)  latest in thread

Message-ID: <37AA43C4.5DDBE65F@krs.ru>
Permalink:  ../37AA43C4.5DDBE65F@krs.ru/
Also on:    postgresql.org/message-id/37AA43C4.5DDBE65F@krs.ru

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: vadim@krs.ru, tgl@sss.pgh.pa.us, a222@redrose.net
  Subject: Re: [SQL] qurey plan and indices
  In-Reply-To: <37AA43C4.5DDBE65F@krs.ru>
  References: <4175.933863966@sss.pgh.pa.us> <37AA43C4.5DDBE65F@krs.ru>

* 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