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 по дате отправления: