pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Patrick Giagnocavo <a222@redrose.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] qurey plan and indices
Date: Thu, 05 Aug 1999 10:39:26 -0400
Message-ID: <4175.933863966@sss.pgh.pa.us> (raw)
In-Reply-To: <37A99D4F.610E6F7C@redrose.net>

Patrick Giagnocavo <a222@redrose.net> writes:
> MESZAROS Attila wrote:
>>
>> I've experienced a brutal speedup (order of 2) separateing the following
>> subquery and making it manually:
>> 
>> explain select name,description
>> from descriptions
>> where in (select name
>> from descriptions
>> where description like '%Bankverbindung%');

> Correct me if I am wrong, however if using 
> LIKE '%something%'
> (which means, the field contains 'something' somewhere in the field)
> there is never a chance to use the index you have created - a
> sequential table scan MUST be made, thus you have to read all 3575
> rows to return the set.

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...

			regards, tom lane



view thread (7+ messages)  latest in thread

Message-ID: <4175.933863966@sss.pgh.pa.us>
Permalink:  ../4175.933863966@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4175.933863966@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, a222@redrose.net
  Subject: Re: [SQL] qurey plan and indices
  In-Reply-To: <4175.933863966@sss.pgh.pa.us>
  References: <37A99D4F.610E6F7C@redrose.net> <4175.933863966@sss.pgh.pa.us>

* 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