Re: Proposal: "query_work_mem" GUC, to distribute working memory to the query's individual operators

On Fri, 2025-01-24 at 17:04 -0800, James Hunter wrote:
> Generating "high memory" vs. "low memory" paths would be tricky,
> because the definition of "high" vs. "low" depends on the entire path
> tree, not just on a single path node. So I think it would quickly
> lead
> to a state-space explosion, as you mention.

At first, it appears to lead to an explosion, but there are a lot of
ways to prune early. Many operators, like an index scan, don't even
need to track memory, so they'd just have the one path. Other operators
can just generate a low memory path because estimates show that it's
unlikely to need more than that. And if there's a blocking operator,
then that resets the memory requirement, pruning the space further.

And I assume you are talking about analytic queries with reasonably
large values of work_mem anyway. That justifies a bit more planning
time -- no need to generate extra paths for cheap queries.

Maybe my idea doesn't work out, but I think it's too early to dismiss
it.

Regards,
    Jeff Davis