| blob | f5202430f807bc279ce5c031769bd71cf42c14e2 |
1 -- Perform tests on the Memoize node.
2 -- The cache hits/misses/evictions from the Memoize node can vary between
3 -- machines. Let's just replace the number with an 'N'. In order to allow us
4 -- to perform validation when the measure was zero, we replace a zero value
5 -- with "Zero". All other numbers are replaced with 'N'.
6 create function explain_memoize(query text, hide_hitmiss bool) returns setof text
7 language plpgsql as
8 $$
9 declare
10 ln text;
11 begin
12 for ln in
13 execute format('explain (analyze, costs off, summary off, timing off) %s',
14 query)
15 loop
16 if hide_hitmiss = true then
17 ln := regexp_replace(ln, 'Hits: 0', 'Hits: Zero');
18 ln := regexp_replace(ln, 'Hits: \d+', 'Hits: N');
19 ln := regexp_replace(ln, 'Misses: 0', 'Misses: Zero');
20 ln := regexp_replace(ln, 'Misses: \d+', 'Misses: N');
21 end if;
22 ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
23 ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
24 ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
25 ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
26 ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
27 return next ln;
28 end loop;
29 end;
30 $$;
31 -- Ensure we get a memoize node on the inner side of the nested loop
32 SET enable_hashjoin TO off;
33 SET enable_bitmapscan TO off;
34 SELECT explain_memoize('
35 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
36 INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty
37 WHERE t2.unique1 < 1000;', false);
38 explain_memoize
39 -------------------------------------------------------------------------------------------
40 Aggregate (actual rows=1 loops=N)
41 -> Nested Loop (actual rows=1000 loops=N)
42 -> Seq Scan on tenk1 t2 (actual rows=1000 loops=N)
43 Filter: (unique1 < 1000)
44 Rows Removed by Filter: 9000
45 -> Memoize (actual rows=1 loops=N)
46 Cache Key: t2.twenty
47 Cache Mode: logical
48 Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
49 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
50 Index Cond: (unique1 = t2.twenty)
51 Heap Fetches: N
52 (12 rows)
54 -- And check we get the expected results.
55 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
56 INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty
57 WHERE t2.unique1 < 1000;
58 count | avg
59 -------+--------------------
60 1000 | 9.5000000000000000
61 (1 row)
63 -- Try with LATERAL joins
64 SELECT explain_memoize('
65 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
66 LATERAL (SELECT t2.unique1 FROM tenk1 t2
67 WHERE t1.twenty = t2.unique1 OFFSET 0) t2
68 WHERE t1.unique1 < 1000;', false);
69 explain_memoize
70 -------------------------------------------------------------------------------------------
71 Aggregate (actual rows=1 loops=N)
72 -> Nested Loop (actual rows=1000 loops=N)
73 -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N)
74 Filter: (unique1 < 1000)
75 Rows Removed by Filter: 9000
76 -> Memoize (actual rows=1 loops=N)
77 Cache Key: t1.twenty
78 Cache Mode: binary
79 Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
80 -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
81 Index Cond: (unique1 = t1.twenty)
82 Heap Fetches: N
83 (12 rows)
85 -- And check we get the expected results.
86 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
87 LATERAL (SELECT t2.unique1 FROM tenk1 t2
88 WHERE t1.twenty = t2.unique1 OFFSET 0) t2
89 WHERE t1.unique1 < 1000;
90 count | avg
91 -------+--------------------
92 1000 | 9.5000000000000000
93 (1 row)
95 -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
96 SET work_mem TO '64kB';
97 SET hash_mem_multiplier TO 1.0;
98 SET enable_mergejoin TO off;
99 -- Ensure we get some evictions. We're unable to validate the hits and misses
100 -- here as the number of entries that fit in the cache at once will vary
101 -- between different machines.
102 SELECT explain_memoize('
103 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
104 INNER JOIN tenk1 t2 ON t1.unique1 = t2.thousand
105 WHERE t2.unique1 < 1200;', true);
106 explain_memoize
107 -------------------------------------------------------------------------------------------
108 Aggregate (actual rows=1 loops=N)
109 -> Nested Loop (actual rows=1200 loops=N)
110 -> Seq Scan on tenk1 t2 (actual rows=1200 loops=N)
111 Filter: (unique1 < 1200)
112 Rows Removed by Filter: 8800
113 -> Memoize (actual rows=1 loops=N)
114 Cache Key: t2.thousand
115 Cache Mode: logical
116 Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
117 -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
118 Index Cond: (unique1 = t2.thousand)
119 Heap Fetches: N
120 (12 rows)
122 CREATE TABLE flt (f float);
123 CREATE INDEX flt_f_idx ON flt (f);
124 INSERT INTO flt VALUES('-0.0'::float),('+0.0'::float);
125 ANALYZE flt;
126 SET enable_seqscan TO off;
127 -- Ensure memoize operates in logical mode
128 SELECT explain_memoize('
129 SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
130 explain_memoize
131 -------------------------------------------------------------------------------
132 Nested Loop (actual rows=4 loops=N)
133 -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
134 Heap Fetches: N
135 -> Memoize (actual rows=2 loops=N)
136 Cache Key: f1.f
137 Cache Mode: logical
138 Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
139 -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
140 Index Cond: (f = f1.f)
141 Heap Fetches: N
142 (10 rows)
144 -- Ensure memoize operates in binary mode
145 SELECT explain_memoize('
146 SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
147 explain_memoize
148 -------------------------------------------------------------------------------
149 Nested Loop (actual rows=4 loops=N)
150 -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
151 Heap Fetches: N
152 -> Memoize (actual rows=2 loops=N)
153 Cache Key: f1.f
154 Cache Mode: binary
155 Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
156 -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
157 Index Cond: (f <= f1.f)
158 Heap Fetches: N
159 (10 rows)
161 DROP TABLE flt;
162 -- Exercise Memoize in binary mode with a large fixed width type and a
163 -- varlena type.
164 CREATE TABLE strtest (n name, t text);
165 CREATE INDEX strtest_n_idx ON strtest (n);
166 CREATE INDEX strtest_t_idx ON strtest (t);
167 INSERT INTO strtest VALUES('one','one'),('two','two'),('three',repeat(fipshash('three'),100));
168 -- duplicate rows so we get some cache hits
169 INSERT INTO strtest SELECT * FROM strtest;
170 ANALYZE strtest;
171 -- Ensure we get 3 hits and 3 misses
172 SELECT explain_memoize('
173 SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
174 explain_memoize
175 ----------------------------------------------------------------------------------
176 Nested Loop (actual rows=24 loops=N)
177 -> Seq Scan on strtest s1 (actual rows=6 loops=N)
178 -> Memoize (actual rows=4 loops=N)
179 Cache Key: s1.n
180 Cache Mode: binary
181 Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
182 -> Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N)
183 Index Cond: (n <= s1.n)
184 (8 rows)
186 -- Ensure we get 3 hits and 3 misses
187 SELECT explain_memoize('
188 SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
189 explain_memoize
190 ----------------------------------------------------------------------------------
191 Nested Loop (actual rows=24 loops=N)
192 -> Seq Scan on strtest s1 (actual rows=6 loops=N)
193 -> Memoize (actual rows=4 loops=N)
194 Cache Key: s1.t
195 Cache Mode: binary
196 Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
197 -> Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N)
198 Index Cond: (t <= s1.t)
199 (8 rows)
201 DROP TABLE strtest;
202 -- Ensure memoize works with partitionwise join
203 SET enable_partitionwise_join TO on;
204 CREATE TABLE prt (a int) PARTITION BY RANGE(a);
205 CREATE TABLE prt_p1 PARTITION OF prt FOR VALUES FROM (0) TO (10);
206 CREATE TABLE prt_p2 PARTITION OF prt FOR VALUES FROM (10) TO (20);
207 INSERT INTO prt VALUES (0), (0), (0), (0);
208 INSERT INTO prt VALUES (10), (10), (10), (10);
209 CREATE INDEX iprt_p1_a ON prt_p1 (a);
210 CREATE INDEX iprt_p2_a ON prt_p2 (a);
211 ANALYZE prt;
212 SELECT explain_memoize('
213 SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
214 explain_memoize
215 ------------------------------------------------------------------------------------------
216 Append (actual rows=32 loops=N)
217 -> Nested Loop (actual rows=16 loops=N)
218 -> Index Only Scan using iprt_p1_a on prt_p1 t1_1 (actual rows=4 loops=N)
219 Heap Fetches: N
220 -> Memoize (actual rows=4 loops=N)
221 Cache Key: t1_1.a
222 Cache Mode: logical
223 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
224 -> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4 loops=N)
225 Index Cond: (a = t1_1.a)
226 Heap Fetches: N
227 -> Nested Loop (actual rows=16 loops=N)
228 -> Index Only Scan using iprt_p2_a on prt_p2 t1_2 (actual rows=4 loops=N)
229 Heap Fetches: N
230 -> Memoize (actual rows=4 loops=N)
231 Cache Key: t1_2.a
232 Cache Mode: logical
233 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
234 -> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4 loops=N)
235 Index Cond: (a = t1_2.a)
236 Heap Fetches: N
237 (21 rows)
239 -- Ensure memoize works with parameterized union-all Append path
240 SET enable_partitionwise_join TO off;
241 SELECT explain_memoize('
242 SELECT * FROM prt_p1 t1 INNER JOIN
243 (SELECT * FROM prt_p1 UNION ALL SELECT * FROM prt_p2) t2
244 ON t1.a = t2.a;', false);
245 explain_memoize
246 -------------------------------------------------------------------------------------
247 Nested Loop (actual rows=16 loops=N)
248 -> Index Only Scan using iprt_p1_a on prt_p1 t1 (actual rows=4 loops=N)
249 Heap Fetches: N
250 -> Memoize (actual rows=4 loops=N)
251 Cache Key: t1.a
252 Cache Mode: logical
253 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
254 -> Append (actual rows=4 loops=N)
255 -> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4 loops=N)
256 Index Cond: (a = t1.a)
257 Heap Fetches: N
258 -> Index Only Scan using iprt_p2_a on prt_p2 (actual rows=0 loops=N)
259 Index Cond: (a = t1.a)
260 Heap Fetches: N
261 (14 rows)
263 DROP TABLE prt;
264 RESET enable_partitionwise_join;
265 -- Exercise Memoize code that flushes the cache when a parameter changes which
266 -- is not part of the cache key.
267 -- Ensure we get a Memoize plan
268 EXPLAIN (COSTS OFF)
269 SELECT unique1 FROM tenk1 t0
270 WHERE unique1 < 3
271 AND EXISTS (
272 SELECT 1 FROM tenk1 t1
273 INNER JOIN tenk1 t2 ON t1.unique1 = t2.hundred
274 WHERE t0.ten = t1.twenty AND t0.two <> t2.four OFFSET 0);
275 QUERY PLAN
276 ----------------------------------------------------------------
277 Index Scan using tenk1_unique1 on tenk1 t0
278 Index Cond: (unique1 < 3)
279 Filter: (SubPlan 1)
280 SubPlan 1
281 -> Nested Loop
282 -> Index Scan using tenk1_hundred on tenk1 t2
283 Filter: (t0.two <> four)
284 -> Memoize
285 Cache Key: t2.hundred
286 Cache Mode: logical
287 -> Index Scan using tenk1_unique1 on tenk1 t1
288 Index Cond: (unique1 = t2.hundred)
289 Filter: (t0.ten = twenty)
290 (13 rows)
292 -- Ensure the above query returns the correct result
293 SELECT unique1 FROM tenk1 t0
294 WHERE unique1 < 3
295 AND EXISTS (
296 SELECT 1 FROM tenk1 t1
297 INNER JOIN tenk1 t2 ON t1.unique1 = t2.hundred
298 WHERE t0.ten = t1.twenty AND t0.two <> t2.four OFFSET 0);
299 unique1
300 ---------
301 2
302 (1 row)
304 RESET enable_seqscan;
305 RESET enable_mergejoin;
306 RESET work_mem;
307 RESET hash_mem_multiplier;
308 RESET enable_bitmapscan;
309 RESET enable_hashjoin;
310 -- Test parallel plans with Memoize
311 SET min_parallel_table_scan_size TO 0;
312 SET parallel_setup_cost TO 0;
313 SET parallel_tuple_cost TO 0;
314 SET max_parallel_workers_per_gather TO 2;
315 -- Ensure we get a parallel plan.
316 EXPLAIN (COSTS OFF)
317 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
318 LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
319 WHERE t1.unique1 < 1000;
320 QUERY PLAN
321 -------------------------------------------------------------------------------
322 Finalize Aggregate
323 -> Gather
324 Workers Planned: 2
325 -> Partial Aggregate
326 -> Nested Loop
327 -> Parallel Bitmap Heap Scan on tenk1 t1
328 Recheck Cond: (unique1 < 1000)
329 -> Bitmap Index Scan on tenk1_unique1
330 Index Cond: (unique1 < 1000)
331 -> Memoize
332 Cache Key: t1.twenty
333 Cache Mode: logical
334 -> Index Only Scan using tenk1_unique1 on tenk1 t2
335 Index Cond: (unique1 = t1.twenty)
336 (14 rows)
338 -- And ensure the parallel plan gives us the correct results.
339 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
340 LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
341 WHERE t1.unique1 < 1000;
342 count | avg
343 -------+--------------------
344 1000 | 9.5000000000000000
345 (1 row)
347 RESET max_parallel_workers_per_gather;
348 RESET parallel_tuple_cost;
349 RESET parallel_setup_cost;
350 RESET min_parallel_table_scan_size;