| Lists: | pgsql-general |
|---|
| From: | "K(dot) Ari Krupnikov" <ari(at)iln(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | performance - self-joins vs. subqueries |
| Date: | 2001-02-12 15:33:24 |
| Message-ID: | 3A880244.4F0C5F5F@iln.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "K(dot) Ari Krupnikov" <ari(at)iln(dot)net> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: performance - self-joins vs. subqueries |
| Date: | 2001-02-12 23:36:41 |
| Message-ID: | 23237.982021001@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
"K. Ari Krupnikov" <ari(at)iln(dot)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
| From: | "K(dot) Ari Krupnikov" <ari(at)iln(dot)net> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: performance - self-joins vs. subqueries |
| Date: | 2001-02-13 00:43:29 |
| Message-ID: | 3A888331.6DF45B27@iln.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Tom Lane wrote:
>
> "K. Ari Krupnikov" <ari(at)iln(dot)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
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "K(dot) Ari Krupnikov" <ari(at)iln(dot)net> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: performance - self-joins vs. subqueries |
| Date: | 2001-02-13 15:48:22 |
| Message-ID: | 4723.982079302@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
"K. Ari Krupnikov" <ari(at)iln(dot)net> writes:
> Tom Lane wrote:
>> "K. Ari Krupnikov" <ari(at)iln(dot)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