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
