Re: [PERFORM] self join revisited

[Matthew Wakeling]

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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance