Horton

Re: A performance issue with Memoize

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: David Rowley <dgrowleyml@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2023-10-25T06:40:58Z
Lists: pgsql-hackers 
On Fri, Oct 20, 2023 at 6:40 PM Richard Guo <guofenglinux@gmail.com> wrote:

> I haven't thought thoroughly about the fix yet.  But one way I'm
> thinking is that in create_subqueryscan_plan() we can first add the
> subquery's subplan_params to root->curOuterParams, and then replace
> outer-relation Vars in scan_clauses afterwards.  That can make us be
> able to share the same PARAM_EXEC slot for the same Var that both
> belongs to the subquery's uplevel vars and to the NestLoop's
> outer-relation vars.  To be concrete, something like attached.
>

After some more thought, I think this is the right way to fix this
issue.  The idea here is to make sure that the same NLP Var shares the
same PARAM_EXEC slot.  This change can also help to save PARAM_EXEC
slots (which is trivial though since slots are very cheap).

Thanks
Richard

Commits

  1. Attempt to fix newly added Memoize regression test

  2. Compare varnullingrels too in assign_param_for_var().

  3. De-dupicate Memoize cache keys

  4. Improve NestLoopParam generation for lateral subqueries

  5. Avoid sharing PARAM_EXEC slots between different levels of NestLoop.