Horton

Re: A performance issue with Memoize

Arne Roland <arne.roland@malkut.net>

View thread View on postgr.es
From: Arne Roland <arne.roland@malkut.net>
To: Richard Guo <guofenglinux@gmail.com>, Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-01-18T06:39:09Z
Lists: pgsql-hackers 
Hi Richard,

I can tell this a real world problem. I have seen this multiple times in 
production.

The fix seems surprisingly simple.

I hope my questions here aren't completely off. I still struggle to 
think about the implications.

I wonder, if there is any stuff we are breaking by bluntly forgetting 
about the subplan params. Maybe some table valued function scan within a 
subquery scan? Or something about casts on a join condition, that could 
be performed differently?

I wasn't able to construct a problem case. I might be just missing 
context here. But I am not yet fully convinced whether this is safe to 
do in all cases.

Regards
Arne

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.