Re: self join revisited

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 <hugo@carfax.nildram.co.uk>