Thread: Should work_mem be stable for a prepared statement?

As a part of this discussion:

https://www.postgresql.org/message-id/CAJVSvF6s1LgXF6KB2Cz68sHzk%2Bv%2BO_vmwEkaon%3DH8O9VcOr-tQ%40mail.gmail.com

James pointed out something interesting, which is that a prepared
statement enforces the work_mem limit at execution time, which might be
different from the work_mem at the time the statement was prepared.

For instance:

   SET work_mem='1GB';
   PREPARE foo AS ...; -- plans using 1GB limit
   SET work_mem='1MB';
   EXECUTE foo; -- enforces 1MB limit

My first reaction is that it's not right because the costing for the
plan is completely bogus with a different work_mem. It would make more
sense to me if we either (a) enforced work_mem as it was at the time of
planning; or (b) replanned if executed with a different work_mem
(similar to how we replan sometimes with different parameters).

But I'm not sure whether someone might be relying on the existing
behavior?

If we were to implement (a) or (b), we couldn't use the work_mem global
directly, we'd need to save it in the plan, and enforce using the
plan's saved value. But that might be a good change anyway. In theory
we might need to do something similar for hash_mem_multiplier, too.

Regards,
    Jeff Davis

On Fri, 28 Feb 2025 at 07:42, Jeff Davis <pgsql@j-davis.com> wrote:
> https://www.postgresql.org/message-id/CAJVSvF6s1LgXF6KB2Cz68sHzk%2Bv%2BO_vmwEkaon%3DH8O9VcOr-tQ%40mail.gmail.com
>
> James pointed out something interesting, which is that a prepared
> statement enforces the work_mem limit at execution time, which might be
> different from the work_mem at the time the statement was prepared.

There's a similar but not quite the same situation with the enable_*
GUCs. The executor isn't going to pick up a new value for these like
it will for work_mem, but I think portions of the same argument can be
made, i.e. Someone might not like that turning off enable_seqscan
after doing PREPARE and EXECUTE once does not invalidate their plan.

> My first reaction is that it's not right because the costing for the
> plan is completely bogus with a different work_mem. It would make more
> sense to me if we either (a) enforced work_mem as it was at the time of
> planning; or (b) replanned if executed with a different work_mem
> (similar to how we replan sometimes with different parameters).

If we were to fix this then a) effectively already happens for the
enable_* GUCs, so b) would be the only logical way to fix.

> But I'm not sure whether someone might be relying on the existing
> behavior?

It looks like there was a bit of discussion on this topic about 18
years ago in [1], but it didn't seem to end with a very conclusive
outcome. I did learn that we once didn't have a method to invalidate
cached plans, so perhaps the current behaviour is a remnant of the
previous lack of infrastructure.

David

[1] https://www.postgresql.org/message-id/15168.1174410673%40sss.pgh.pa.us

David Rowley <dgrowleyml@gmail.com> writes:
> On Fri, 28 Feb 2025 at 07:42, Jeff Davis <pgsql@j-davis.com> wrote:
>> My first reaction is that it's not right because the costing for the
>> plan is completely bogus with a different work_mem. It would make more
>> sense to me if we either (a) enforced work_mem as it was at the time of
>> planning; or (b) replanned if executed with a different work_mem
>> (similar to how we replan sometimes with different parameters).

> If we were to fix this then a) effectively already happens for the
> enable_* GUCs, so b) would be the only logical way to fix.

Given that nobody's complained about this for twenty-plus years,
I can't get excited about adding complexity to do either thing.

            regards, tom lane

On Thu, 2025-02-27 at 17:04 -0500, Tom Lane wrote:
> Given that nobody's complained about this for twenty-plus years,
> I can't get excited about adding complexity to do either thing.

I had in mind some refactoring in this area, which ideally would not
add complexity. It might provide some nice benefits, but would
introduce this behavior change, which makes it slightly more than a
refactoring.

It sounds like the behavior change would be desirable or at least
neutral. I will have to try it out and see if the refactoring is a net
improvement or turns into a mess.

Regards,
    Jeff Davis

> It sounds like the behavior change would be desirable or at least
> neutral. I will have to try it out and see if the refactoring is a net
> improvement or turns into a mess.

I think this is a good operational improvement, particularly if
someone wants to change work_mem in a pinch, and the only
option now they have it to somehow get the application to
re-prepare; deallocating all prepared statements or reconnecting.
This is even worse with extended query protocol prepared statements
in which there is no visibility in pg_prepared_statements. So one may
be forced to use DEALLOCATE ALL.

However, I think any GUC that can influence the planner
should be considered for consistency in behavior.
It was mentioned above with the enable_* GUCs, but another
one I can think of is the max_parallel_workers_per_gather which
should then force a re-plan if changed. I have seen users need to turn
that off in a hurry when it impacts their oltp workload.


--
Sami Imseih
Amazon Web Services (AWS)

Sami Imseih <samimseih@gmail.com> writes:
> However, I think any GUC that can influence the planner
> should be considered for consistency in behavior.
> It was mentioned above with the enable_* GUCs, but another
> one I can think of is the max_parallel_workers_per_gather which
> should then force a re-plan if changed. I have seen users need to turn
> that off in a hurry when it impacts their oltp workload.

The argument for treating work_mem specially is that it has effects at
both plan time and run time, so that the planner's cost assumptions
are invalidated if the executor uses a different value than the
planner did.  I don't believe that argument applies to anything else;
certainly it doesn't apply to the enable_* flags.  I'm also not
convinced that the argument requires us to solve the problem by
re-planning.  It would work just as well to stamp each PlannedStmt
with the value that the planner used and refer to that in the
executor instead of looking directly at the GUC.

This is all kind of moot though, now that Jeff has revealed that
what he's really interested in is some sort of refactoring.
Maybe that refactoring is one that would conveniently apply to
other GUCs, or maybe it isn't.  I'm content to await details
before arguing about what we "should" do.

            regards, tom lane

> The argument for treating work_mem specially is that it has effects at
> both plan time and run time, so that the planner's cost assumptions
> are invalidated if the executor uses a different value than the
> planner did.

I see that now. Thanks!

> Maybe that refactoring is one that would conveniently apply to
> other GUCs, or maybe it isn't.  I'm content to await details
> before arguing about what we "should" do.

Agree.

--
Sami

> I'm also not convinced that the argument requires us to solve
> the problem by re-planning.  It would work just as well to stamp
> each PlannedStmt with the value that the planner used and
> refer to that in the executor instead of looking directly at the GUC.

hmm, if work_mem influences the plan, can we really avoid re-planning?
Here is an example where re-plannning is required to yield a plan
that is based on the current work_mem. right?

postgres=# show work_mem ;
 work_mem
----------
 4MB
(1 row)

postgres=# prepare sortprep as select * from pg_class order by oid ;
PREPARE
postgres=# explain execute sortprep;
                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=36.20..37.23 rows=415 width=273)
   Sort Key: oid
   ->  Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=273)
(3 rows)

postgres=# set work_mem = "64kB";
SET
postgres=# explain execute sortprep;
                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=36.20..37.23 rows=415 width=273)
   Sort Key: oid
   ->  Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=273)
(3 rows)

postgres=# explain  select * from pg_class order by oid ;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.27..60.85
rows=415 width=273)
(1 row)

--

Sami