From: Andrei Lepikhov <lepihov@gmail.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
To: Richard Guo <guofenglinux@gmail.com>
Subject: Volatile functions under Memoize node
Date: Thu, 19 Sep 2024 18:47:30 +0200
Message-ID: <7ab4aac3-4790-416f-98b8-2ec0707cceb5@gmail.com> (raw)
Hi,
Excuse me if I made noise in vain. After discovering the limits of the
Memoize node, I realized that volatile functions are allowed under the
Memoize. Example:
DROP TABLE IF EXISTS base, other CASCADE;
CREATE TABLE base (x numeric, y text, x1 integer);
INSERT INTO base (x,y,x1) SELECT 1, 'abccccccccccccc'||1,1 FROM
generate_series(1,1E6) AS x;
CREATE TABLE other (x numeric, y text, x1 integer);
INSERT INTO other (x,y,x1) SELECT x, 'abccccccccccccc'||x,1 FROM
generate_series(1,1E4) AS x;
VACUUM ANALYZE base, other;
EXPLAIN (COSTS OFF)
SELECT * FROM base WHERE base.x IN (
SELECT o2.x FROM other o1 LEFT JOIN other o2 ON (o1.x=o2.x) LEFT JOIN
other o3 ON (o2.x=o3.x+random())
WHERE base.x = o1.x GROUP BY o2.x ORDER BY o2.x
);
/*
Nested Loop
-> Seq Scan on base
-> Memoize
Cache Key: base.x
Cache Mode: binary
-> Subquery Scan on "ANY_subquery"
Filter: (base.x = "ANY_subquery".x)
-> Group
Group Key: o2.x
-> Sort
Sort Key: o2.x
-> Nested Loop Left Join
Join Filter: ((o2.x)::double precision
= ((o3.x)::double precision + random()))
-> Nested Loop Left Join
-> Seq Scan on other o1
Filter: (base.x = x)
-> Seq Scan on other o2
Filter: (x = base.x)
-> Seq Scan on other o3
*/
In my mind, any volatile function in any expression should reject the
usage of Memoize, am I wrong? I haven't discovered this case deeply yet,
but maybe someone has a quick and short answer to this question.
--
regards, Andrei Lepikhov
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, pgsql-bugs@lists.postgresql.org, guofenglinux@gmail.com
Subject: Re: Volatile functions under Memoize node
In-Reply-To: <7ab4aac3-4790-416f-98b8-2ec0707cceb5@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox