From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | terry(at)ashtonwoodshomes(dot)com |
Cc: | "Postgres (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using the IN clauise |
Date: | 2002-10-25 18:51:57 |
Message-ID: | 1035571917.12582.61.camel@camel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 2002-10-25 at 14:40, terry(at)ashtonwoodshomes(dot)com wrote:
> My post didn't go through the first time, so here goes again:
> >
> >
> > I thought I read somewhere that it was better to avoid using
> > the IN clause (at least when that means doing a subselect)
> > for efficiency reasons, but I cannot find it on the website now.
> >
> > Does anyone know where that is?
> >
Do a search on the interactive docs for IN or EXISTS, I think it's
mentioned there. Either way it is generally recommended to use the
EXISTS grammer rather than the IN for performance reasons. When in
doubt, explain analyze is your friend.
> > And can someone confirm the following:
> >
> > This:
> > SELECT t1.f1
> > FROM t1
> > WHERE t1.f1 IN (select f1 from t2 AS t2 where t2.f1 = t1.f1
> > AND t2.f2 = 'v1')
> >
> > is generally slower to run then:
> > SELECT t1.f1
> > FROM t1, t2
> > WHERE t1.f1 = t2.f1
> > AND t2.f3 = 'v1'
> >
I would believe it to be true, though depending on your table / data
structure I can't say 100% percent true. Try running explain analyze on
both queries and see what you return.
> > Thanks
> >
> > Terry Fielder
> > Network Engineer
> > Great Gulf Homes / Ashton Woods Homes
> > terry(at)greatgulfhomes(dot)com
> >
Robert Treat
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2002-10-25 18:52:48 | gborg redirect problem |
Previous Message | terry | 2002-10-25 18:40:00 | Re: Using the IN clauise |