Check volatile functions in ppi_clauses for memoize node

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Check volatile functions in ppi_clauses for memoize node
Date: 2023-08-04 10:26:21
Message-ID: CAMbWs49nHFnHbpepLsv_yF3qkpCS4BdB-v8HoJVv8_=Oat0u_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In get_memoize_path() we have a theory about avoiding memoize node if
there are volatile functions in the inner rel's target/restrict list.

/*
* We can't use a memoize node if there are volatile functions in the
* inner rel's target list or restrict list. A cache hit could reduce the
* number of calls to these functions.
*/
if (contain_volatile_functions((Node *) innerrel->reltarget))
return NULL;

foreach(lc, innerrel->baserestrictinfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);

if (contain_volatile_functions((Node *) rinfo))
return NULL;
}

It seems that the check for restrict list is not thorough, because for a
parameterized scan we are supposed to also consider all the join clauses
available from the outer rels, ie, ppi_clauses. For instance,

create table t (a float);
insert into t values (1.0), (1.0), (1.0), (1.0);
analyze t;

explain (costs off)
select * from t t1 left join lateral
(select t1.a as t1a, t2.a as t2a from t t2) s
on t1.a = s.t2a + random();
QUERY PLAN
-----------------------------------------------
Nested Loop Left Join
-> Seq Scan on t t1
-> Memoize
Cache Key: t1.a
Cache Mode: binary
-> Seq Scan on t t2
Filter: (t1.a = (a + random()))
(7 rows)

According to the theory we should not use memoize node for this query
because of the volatile function in the inner side. So propose a patch
to fix that.

Thanks
Richard

Attachment Content-Type Size
v1-0001-Check-volatile-functions-in-ppi_clauses-for-memoize-node.patch application/octet-stream 1.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2023-08-04 10:47:38 Re: [RFC] Clang plugin for catching suspicious typedef casting
Previous Message tender wang 2023-08-04 10:10:53 Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails