Volatile functions under Memoize node
От | Andrei Lepikhov |
---|---|
Тема | Volatile functions under Memoize node |
Дата | |
Msg-id | 7ab4aac3-4790-416f-98b8-2ec0707cceb5@gmail.com обсуждение исходный текст |
Ответы |
Re: Volatile functions under Memoize node
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: