From: Rikard Pavelic <rikard.pavelic@zg.htnet.hr>
To: pgsql-performance@postgresql.org
Subject: self join revisited
Date: Wed, 01 Apr 2009 18:30:23 +0200
Message-ID: <49D3969F.4030701@zg.htnet.hr> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rikard.pavelic@zg.htnet.hr
Subject: Re: self join revisited
In-Reply-To: <49D3969F.4030701@zg.htnet.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox