Skip site navigation (1) Skip section navigation (2)

Re: SQL 'in' vs join.

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: mlw <markw(at)mohawksoft(dot)com>
Cc: Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SQL 'in' vs join.
Date: 2000-11-30 15:26:02
Message-ID: 3A26718A.6BA50275@tm.ee (view raw or flat)
Thread:
Lists: pgsql-hackers
mlw wrote:
> 
> Why is a "select * from table1 where field in (select field from table2
> where condition )"
> 
> is so dramatically bad compared to:
> 
> "select * from table1, table2 where table1.field = table2.field and
> condition"
> 
> I can't understand why the first query isn't optimized better than the
> second one. The 'in' query forces a full table scan (it shouldn't) and
> the second one uses the indexes. Does anyone know why?

Its not done yet, and probably hsomewhat difficult to do in a general
fashion

> I know I am no SQL guru, but my gut tells me that the 'in' operator
> should be far more efficient than a join.
> 
> Here are the actual queries:
> 
> cdinfo=# explain select trackid from zsong where muzenbr in (select
> muzenbr from ztitles where title = 'Mulan') ;

try

explain
 select trackid
   from zsong
  where muzenbr in (
         select muzenbr
           from ztitles
          where title = 'Mulan'
            and ztitles.muzenbr=zsong.muzenbr
  );

this should hint the current optimizer to do the right thing;

-----------------
Hannu

In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2000-11-30 15:52:39
Subject: Re: SQL 'in' vs join.
Previous:From: Don BaccusDate: 2000-11-30 15:24:30
Subject: Re: SQL 'in' vs join.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group