Re: Automatically setting work_mem

Tom,

On 3/17/06 12:18 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> One user with ability to enter arbitrary SQL commands can *always* blow
> your resource planning away. Blaming such things on work_mem is
> seriously misguided.

Agreed - that's why we need to split this discussion into the two categories
of (1) scheduling for concurrency protection and (2) dynamic resource
allocation.

Topic (1) is best handled by statement queuing IMO and as demonstrated by
other commercial DBMS. This allows queues of different resource demands to
be used for ensuring that statements can not over consume memory, temp disk,
etc, and that queries with large requirements for some or all of those can
be allocated as much as possible, and those with smaller requirements will
be run (likely at much higher rates) while longer running queries take up
the larger resource pool.

(2) is what this thread is mostly talking about, and the dynamic allocation
of memory to plan nodes (sort, hash) needs to be done so that we are much
more efficient in memory footprint and give more where it's needed. (2)
will require some way of putting an overall memory footprint to a statement,
then sub-allocating within it. I suggest we assume that the overall memory
footprint is constrained somehow, perhaps another GUC that describes a per
statement maximum memory consumption, then at plan time we determine the
sub-allocations that best achieve the plan. This would fit within a scheme
for (1) when we develop one.

- Luke