self join revisited

Поиск
Список
Период
Сортировка
От Rikard Pavelic
Тема self join revisited
Дата
Msg-id 49D3969F.4030701@zg.htnet.hr
обсуждение исходный текст
Ответы Re: self join revisited  (Matthew Wakeling <matthew@flymine.org>)
Re: self join revisited  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Stef Telford
Дата:
Сообщение: Re: Raid 10 chunksize
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Raid 10 chunksize