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(at)carfax(dot)nildram(dot)co(dot)uk>