1-- Perform tests on the Memoize node.
2
3-- The cache hits/misses/evictions from the Memoize node can vary between
4-- machines.  Let's just replace the number with an 'N'.  In order to allow us
5-- to perform validation when the measure was zero, we replace a zero value
6-- with "Zero".  All other numbers are replaced with 'N'.
7create function explain_memoize(query text, hide_hitmiss bool) returns setof text
8language plpgsql as
9$$
10declare
11    ln text;
12begin
13    for ln in
14        execute format('explain (analyze, costs off, summary off, timing off) %s',
15            query)
16    loop
17        if hide_hitmiss = true then
18                ln := regexp_replace(ln, 'Hits: 0', 'Hits: Zero');
19                ln := regexp_replace(ln, 'Hits: \d+', 'Hits: N');
20                ln := regexp_replace(ln, 'Misses: 0', 'Misses: Zero');
21                ln := regexp_replace(ln, 'Misses: \d+', 'Misses: N');
22        end if;
23        ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
24        ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
25        ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
26	ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
27	ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
28        return next ln;
29    end loop;
30end;
31$$;
32
33-- Ensure we get a memoize node on the inner side of the nested loop
34SET enable_hashjoin TO off;
35SET enable_bitmapscan TO off;
36
37SELECT explain_memoize('
38SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
39INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty
40WHERE t2.unique1 < 1000;', false);
41
42-- And check we get the expected results.
43SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
44INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty
45WHERE t2.unique1 < 1000;
46
47-- Try with LATERAL joins
48SELECT explain_memoize('
49SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
50LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
51WHERE t1.unique1 < 1000;', false);
52
53-- And check we get the expected results.
54SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
55LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
56WHERE t1.unique1 < 1000;
57
58-- Reduce work_mem so that we see some cache evictions
59SET work_mem TO '64kB';
60SET enable_mergejoin TO off;
61-- Ensure we get some evictions.  We're unable to validate the hits and misses
62-- here as the number of entries that fit in the cache at once will vary
63-- between different machines.
64SELECT explain_memoize('
65SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
66INNER JOIN tenk1 t2 ON t1.unique1 = t2.thousand
67WHERE t2.unique1 < 1200;', true);
68RESET enable_mergejoin;
69RESET work_mem;
70RESET enable_bitmapscan;
71RESET enable_hashjoin;
72
73-- Test parallel plans with Memoize
74SET min_parallel_table_scan_size TO 0;
75SET parallel_setup_cost TO 0;
76SET parallel_tuple_cost TO 0;
77SET max_parallel_workers_per_gather TO 2;
78
79-- Ensure we get a parallel plan.
80EXPLAIN (COSTS OFF)
81SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
82LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
83WHERE t1.unique1 < 1000;
84
85-- And ensure the parallel plan gives us the correct results.
86SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
87LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
88WHERE t1.unique1 < 1000;
89
90RESET max_parallel_workers_per_gather;
91RESET parallel_tuple_cost;
92RESET parallel_setup_cost;
93RESET min_parallel_table_scan_size;
94