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:52:39
Message-ID: 3A2677C7.5B0974B2@tm.ee (view raw or flat)
Thread:
Lists: pgsql-hackers
mlw wrote:
> 
> Hannu Krosing wrote:
> >
> > 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

did you have indexes on both ztitles.muzenbr and zsong.muzenbr ?

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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-11-30 15:52:41
Subject: Re: SQL 'in' vs join.
Previous:From: Hannu KrosingDate: 2000-11-30 15:26:02
Subject: Re: SQL 'in' vs join.

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