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@xxxxxxxxxxxxxxxxxxxx> -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
