Thread: performance - self-joins vs. subqueries

Many queries can be formulated as either a self-join or a subquery on
the same table.

Is there any difference in performance, or does the analyzer treat them
the same?


--
K. Ari Krupnikov

DBDOM - bridging XML and relational databases
http://www.iter.co.il

"K. Ari Krupnikov" <ari@iln.net> writes:
> Many queries can be formulated as either a self-join or a subquery on
> the same table.
> Is there any difference in performance, or does the analyzer treat them
> the same?

You're a lot better off with a self-join, at present.

            regards, tom lane

Tom Lane wrote:
>
> "K. Ari Krupnikov" <ari@iln.net> writes:
> > Many queries can be formulated as either a self-join or a subquery on
> > the same table.
> > Is there any difference in performance, or does the analyzer treat them
> > the same?
>
> You're a lot better off with a self-join, at present.

At present - is there a change planned?

Could you elaborate a little on how such queries are expanded?

--
K. Ari Krupnikov

DBDOM - bridging XML and relational databases
http://www.iter.co.il

"K. Ari Krupnikov" <ari@iln.net> writes:
> Tom Lane wrote:
>> "K. Ari Krupnikov" <ari@iln.net> writes:
> Many queries can be formulated as either a self-join or a subquery on
> the same table.
> Is there any difference in performance, or does the analyzer treat them
> the same?
>>
>> You're a lot better off with a self-join, at present.

> At present - is there a change planned?

Currently, EXISTS and IN sub-queries are always done by nested loops.
Sooner or later we will try to convert them into self-joins
automatically, so that smarter join methods can be used.  But for now
you need to write them as joins to begin with, if you want 'em to be
fast.

            regards, tom lane