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