Re: self join revisited

From: Matthew Wakeling
Subject: Re: self join revisited
Date: ,
Msg-id: alpine.DEB.2.00.0904011743290.21772@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: self join revisited  (Rikard Pavelic)
List: pgsql-performance

Tree view

self join revisited  (Rikard Pavelic, )
 Re: self join revisited  (Matthew Wakeling, )
 Re: self join revisited  (Tom Lane, )
  Re: self join revisited  (Rikard Pavelic, )
   Re: self join revisited  (Robert Haas, )

On Wed, 1 Apr 2009, Rikard Pavelic wrote:
> It would be great if Postgres could rewrite this query
>
> SELECT bt1.id, bt1.total, sq.id, sq.total
> FROM
>     big_table bt1
>     INNER JOIN small_table st1 on st1.big_id = bt1.id
>     INNER JOIN
>     (
>         SELECT bt2.id, st2.total
>         FROM
>             big_table bt2
>             INNER JOIN small_table st2 on st2.big_id = bt2.id
>         WHERE
>             st2.total > 100
>     ) sq ON sq.id = bt1.id
> WHERE
>     st1.total<200
>
> like this
>
> SELECT bt1.id, bt1.total, bt1.id, st2.total
> FROM
>     big_table bt1
>     INNER JOIN small_table st1 on st1.big_id = bt1.id
>     INNER JOIN small_table st2 on st2.big_id = bt1.id AND st2.total > 100
> WHERE
>     st1.total<200

Those queries are only equivalent if big_table.id is unique. However, even
so some benefit could be gained from a self-join algorithm. For instance,
if given some rather evil cleverness, it could be adapted to calculate
overlaps very quickly.

However, a self-join is very similar to a merge join, and the benefit over
a standard merge join would be small.

Matthew

--
"We did a risk management review.  We concluded that there was no risk
 of any management."        -- Hugo Mills <>


pgsql-performance by date:

From: Scott Carey
Date:
Subject: Re: Raid 10 chunksize
From: david@lang.hm
Date:
Subject: Re: Raid 10 chunksize