Re: [PERFORM] repeated subplan execution

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [PERFORM] repeated subplan execution
Дата
Msg-id CAMkU=1yOhtRXEqLORJ28bHMz_HuBwzSHhaRAq3q1ixOHgWOFoQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] repeated subplan execution  (monika yadav <monika.1234yadav@gmail.com>)
Ответы Re: [PERFORM] repeated subplan execution
Список pgsql-performance
Дерево обсуждения
On Tue, Sep 19, 2017 at 7:31 PM, monika yadav <monika.1234yadav@gmail.com> wrote:
Hi All,

I didn't understand why same sub plan for the sub query executed two times? As per the query it should have been executed only once.

Can someone please explain this behaviour of query execution ? 


The sum_bid at the end of the query is an alias for the entire subselect, so it not entirely surprising that it gets interpolated twice. it is just kind of unfortunate from a performance perspective.

The query I originally gave is equivalent to this query:


 select
        aid,
        (select sum(bid) from pgbench_branches
            where bbalance between -10000-abalance and 1+abalance
        ) as sum_bid
        from pgbench_accounts
        where aid between 1 and 1000
        group by aid 
        having (select sum(bid) from pgbench_branches where bbalance between -10000-abalance and 1+abalance ) >0;


In my originally query I just wrapped the whole thing in another select, so that I could use the alias rather than having to mechanically repeat the entire subquery again in the HAVING section.  They give identical plans.

Cheers,

Jeff

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

Предыдущее
От: Mike Broers
Дата:
Сообщение: Re: [PERFORM] query of partitioned object doesnt use index in qa
Следующее
От: David Rowley
Дата:
Сообщение: Re: [PERFORM] query of partitioned object doesnt use index in qa