Message-ID: <49D3969F.4030701@zg.htnet.hr>
Date: Wed, 01 Apr 2009 18:30:23 +0200
From: Rikard Pavelic <rikard.pavelic@zg.htnet.hr>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: self join revisited
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

How hard would it be to teach planer to optimize self join?

While this query which demonstrates it is not that common

SELECT count(*)
FROM
	big_table a
	INNER JOIN big_table b ON a.id = b.id;

This type of query (self joining large table) is very common
(at least in our environment because of heavy usage of views).

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

Regards,
Rikard