Re: [PERFORM] repeated subplan execution

[monika yadav]

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 ?

Thanks a lot.

On Wed, 20 Sep 2017 at 5:01 AM, Jeff Janes  wrote:

> I have a complicated query which runs the exact same subplan more than
> once.
>
> Here is a greatly simplified (and rather pointless) query to replicate the
> issue:
>
> select aid, sum_bid from
> (select
> aid,
> (select sum(bid) from pgbench_branches
> where bbalance between -1-abalance and 1+abalance
> ) as sum_bid
> from pgbench_accounts
> where aid between 1 and 1000
> group by aid
> ) asdfsadf
> where sum_bid >0;
>
>  QUERY
> PLAN
>
> -
>  Group  (cost=0.44..375841.29 rows=931 width=12) (actual
> time=1.233..691.200 rows=679 loops=1)
>Group Key: pgbench_accounts.aid
>Filter: ((SubPlan 2) > 0)
>Rows Removed by Filter: 321
>->  Index Scan using pgbench_accounts_pkey on pgbench_accounts
>  (cost=0.44..634.32 rows=931 width=8) (actual time=0.040..1.783 rows=1000
> loops=1)
>  Index Cond: ((aid >= 1) AND (aid <= 1000))
>SubPlan 2
>  ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
> time=0.406..0.407 rows=1 loops=1000)
>->  Seq Scan on pgbench_branches pgbench_branches_1
>  (cost=0.00..403.00 rows=1 width=4) (actual time=0.392..0.402 rows=1
> loops=1000)
>  Filter: ((bbalance >= ('-1'::integer -
> pgbench_accounts.abalance)) AND (bbalance <= (1 +
> pgbench_accounts.abalance)))
>  Rows Removed by Filter: 199
>SubPlan 1
>  ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
> time=0.407..0.407 rows=1 loops=679)
>->  Seq Scan on pgbench_branches  (cost=0.00..403.00 rows=1
> width=4) (actual time=0.388..0.402 rows=1 loops=679)
>  Filter: ((bbalance >= ('-1'::integer -
> pgbench_accounts.abalance)) AND (bbalance <= (1 +
> pgbench_accounts.abalance)))
>  Rows Removed by Filter: 199
>  Planning time: 0.534 ms
>  Execution time: 691.784 ms
>
>
> https://explain.depesz.com/s/Xaib
>
>
> The subplan is not so fast that I wish it to be executed again or every
> row which passes the filter.
>
> I can prevent this dual execution using a CTE, but that creates other
> problems.  Is there a way to get rid of it without resorting to that?
>
> Maybe also a question for bugs and/or hackers, is why should I need to do
> anything special to avoid dual execution?
>
> Cheers,
>
> Jeff
>

[PERFORM] repeated subplan execution

[Jeff Janes]

I have a complicated query which runs the exact same subplan more than once.

Here is a greatly simplified (and rather pointless) query to replicate the
issue:

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

 QUERY
PLAN
-
 Group  (cost=0.44..375841.29 rows=931 width=12) (actual
time=1.233..691.200 rows=679 loops=1)
   Group Key: pgbench_accounts.aid
   Filter: ((SubPlan 2) > 0)
   Rows Removed by Filter: 321
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts
 (cost=0.44..634.32 rows=931 width=8) (actual time=0.040..1.783 rows=1000
loops=1)
 Index Cond: ((aid >= 1) AND (aid <= 1000))
   SubPlan 2
 ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
time=0.406..0.407 rows=1 loops=1000)
   ->  Seq Scan on pgbench_branches pgbench_branches_1
 (cost=0.00..403.00 rows=1 width=4) (actual time=0.392..0.402 rows=1
loops=1000)
 Filter: ((bbalance >= ('-1'::integer -
pgbench_accounts.abalance)) AND (bbalance <= (1 +
pgbench_accounts.abalance)))
 Rows Removed by Filter: 199
   SubPlan 1
 ->  Aggregate  (cost=403.00..403.01 rows=1 width=8) (actual
time=0.407..0.407 rows=1 loops=679)
   ->  Seq Scan on pgbench_branches  (cost=0.00..403.00 rows=1
width=4) (actual time=0.388..0.402 rows=1 loops=679)
 Filter: ((bbalance >= ('-1'::integer -
pgbench_accounts.abalance)) AND (bbalance <= (1 +
pgbench_accounts.abalance)))
 Rows Removed by Filter: 199
 Planning time: 0.534 ms
 Execution time: 691.784 ms


https://explain.depesz.com/s/Xaib


The subplan is not so fast that I wish it to be executed again or every row
which passes the filter.

I can prevent this dual execution using a CTE, but that creates other
problems.  Is there a way to get rid of it without resorting to that?

Maybe also a question for bugs and/or hackers, is why should I need to do
anything special to avoid dual execution?

Cheers,

Jeff

Re: [PERFORM] https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server

[Tomas Vondra]

On 09/19/2017 02:49 AM, 園田祥平 wrote:
> Hi experts,
> 
> For an academic experiment I need to *restrict the total amount of
> memory that is available for a pgSQL server* to compute a given set of
> queries.
> 
> I know that I can do this through |postgressql.conf|file, where I can
> adjust some parameters related with Resource Management.
> 
> The problem is that: it's not clear for me--given the several parameters
> available on the config file--which is the parameter that I should change. 
> > When I first opened the config file I'm expecting someting like
> this: |max_server_memmory|. Instead I found a lot
> of: |shared_buffers|, |temp_buffers|, |work_mem|, and so on...
> 
> Given that, I've consulted pgSQL docs. on Resource Consumption
>  and
> I come up with the |shared_buffers| as the best candidate for what I'm
> looking for: *the parameter that restricts the total amount of memory
> that a pgSQL server can use to perform its computation*. But I'm not
> completely sure about this. 
> 
> Can you guys give me some insight about which parameters should I adjust
> to restrict the pgSQL server's memory, please?
> 

The short answer is "You can't do that from within PostgreSQL alone."
You can define size of some memory buffers, but not some hard total
limit. One reason is that queries may use multiple work_mem buffers, we
don't know how much memory the other queries are consuming, etc. We also
don't have any control over page cache, for example.

If you really need to do that, you'll need to do that at the OS level,
e.g. by specifying "mem=X" kernel parameter, at the VM level, or
something like that.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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