From 99a23dfae4415a3eb88c3847deefae3c2103077d Mon Sep 17 00:00:00 2001 From: Evdokimov Ilia Date: Tue, 15 Apr 2025 00:47:26 +0300 Subject: [PATCH v7 1/2] Show ndistinct and est_entries in EXPLAIN for Memoize --- src/backend/commands/explain.c | 13 ++++ src/backend/optimizer/path/costsize.c | 3 + src/backend/optimizer/plan/createplan.c | 10 ++- src/backend/optimizer/util/pathnode.c | 3 + src/include/nodes/pathnodes.h | 2 + src/include/nodes/plannodes.h | 6 ++ src/test/regress/expected/create_index.out | 3 +- src/test/regress/expected/join.out | 64 +++++++++++--------- src/test/regress/expected/memoize.out | 54 +++++++++++------ src/test/regress/expected/partition_join.out | 18 ++++-- src/test/regress/expected/subselect.out | 10 +-- 11 files changed, 126 insertions(+), 60 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 786ee865f14..0a4ce5ee7d7 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -3628,6 +3628,19 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es) ExplainPropertyText("Cache Key", keystr.data, es); ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es); + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ExplainIndentText(es); + appendStringInfo(es->str, "Estimated Capacity: %u Estimated Distinct Lookup Keys: %0.0f\n", + ((Memoize *) plan)->est_entries, + ((Memoize *) plan)->est_unique_keys); + } + else + { + ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es); + ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es); + } + pfree(keystr.data); if (!es->analyze) diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 60b0fcfb6be..f72319d903c 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath, mpath->est_entries = Min(Min(ndistinct, est_cache_entries), PG_UINT32_MAX); + /* Remember ndistinct for a potential EXPLAIN later */ + mpath->est_unique_keys = ndistinct; + /* * When the number of distinct parameter values is above the amount we can * store in the cache, then we'll have to evict some entries from the diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index a8f22a8c154..a1456c9014d 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -284,7 +284,8 @@ static Material *make_material(Plan *lefttree); static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations, List *param_exprs, bool singlerow, bool binary_mode, - uint32 est_entries, Bitmapset *keyparamids); + uint32 est_entries, Bitmapset *keyparamids, + double est_unique_keys); static WindowAgg *make_windowagg(List *tlist, WindowClause *wc, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations, @@ -1703,7 +1704,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags) plan = make_memoize(subplan, operators, collations, param_exprs, best_path->singlerow, best_path->binary_mode, - best_path->est_entries, keyparamids); + best_path->est_entries, keyparamids, + best_path->est_unique_keys); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -6636,7 +6638,8 @@ materialize_finished_plan(Plan *subplan) static Memoize * make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations, List *param_exprs, bool singlerow, bool binary_mode, - uint32 est_entries, Bitmapset *keyparamids) + uint32 est_entries, Bitmapset *keyparamids, + double est_unique_keys) { Memoize *node = makeNode(Memoize); Plan *plan = &node->plan; @@ -6654,6 +6657,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations, node->binary_mode = binary_mode; node->est_entries = est_entries; node->keyparamids = keyparamids; + node->est_unique_keys = est_unique_keys; return node; } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 93e73cb44db..1fbcda99067 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1701,6 +1701,9 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Assert(enable_memoize); pathnode->path.disabled_nodes = subpath->disabled_nodes; + /* Estimated number of distinct memoization keys, computed using estimate_num_groups() */ + pathnode->est_unique_keys = 0; + /* * Add a small additional charge for caching the first entry. All the * harder calculations for rescans are performed in cost_memoize_rescan(). diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index bb678bdcdcd..07d97dc0b5b 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2138,6 +2138,8 @@ typedef struct MemoizePath uint32 est_entries; /* The maximum number of entries that the * planner expects will fit in the cache, or 0 * if unknown */ + double est_unique_keys; /* Estimated number of distinct memoization keys, + * used for cache size evaluation. Kept for EXPLAIN */ } MemoizePath; /* diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 658d76225e4..3d9d3a1159d 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -1063,6 +1063,12 @@ typedef struct Memoize /* paramids from param_exprs */ Bitmapset *keyparamids; + + /* + * Estimated number of distinct memoization keys, + * used for cache size evaluation. Kept for EXPLAIN + */ + double est_unique_keys; } Memoize; /* ---------------- diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 9ade7b835e6..826018a8a9f 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2245,10 +2245,11 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON -> Memoize Cache Key: tenk1.hundred Cache Mode: logical + Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100 -> Index Scan using tenk2_hundred on tenk2 Index Cond: (hundred = tenk1.hundred) Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2)) -(10 rows) +(11 rows) -- -- Check behavior with duplicate index column contents diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 14da5708451..eca6ce2c6f0 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2729,8 +2729,8 @@ select * from onek t1 left join lateral (select * from onek t3 where t3.two = t2.two offset 0) s on t2.unique1 = 1; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------ Nested Loop Left Join -> Seq Scan on onek t1 -> Materialize @@ -2740,9 +2740,10 @@ select * from onek t1 -> Memoize Cache Key: t2.two Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Seq Scan on onek t3 Filter: (two = t2.two) -(11 rows) +(12 rows) -- -- check a case where we formerly got confused by conflicting sort orders @@ -5128,8 +5129,8 @@ select * from on i8.q2 = 123, lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss where t1.f1 = ss.f1; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Nested Loop Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1 Join Filter: (t1.f1 = t2.f1) @@ -5146,11 +5147,12 @@ where t1.f1 = ss.f1; Output: (i8.q1), t2.f1 Cache Key: i8.q1 Cache Mode: binary + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 -> Limit Output: (i8.q1), t2.f1 -> Seq Scan on public.text_tbl t2 Output: i8.q1, t2.f1 -(20 rows) +(21 rows) select * from text_tbl t1 @@ -5171,8 +5173,8 @@ select * from lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, lateral (select ss1.* from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Nested Loop Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1) Join Filter: (t1.f1 = (t2.f1)) @@ -5191,6 +5193,7 @@ where t1.f1 = ss2.f1; Output: (i8.q1), t2.f1 Cache Key: i8.q1 Cache Mode: binary + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 -> Limit Output: (i8.q1), t2.f1 -> Seq Scan on public.text_tbl t2 @@ -5199,11 +5202,12 @@ where t1.f1 = ss2.f1; Output: ((i8.q1)), (t2.f1) Cache Key: (i8.q1), t2.f1 Cache Mode: binary + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 -> Limit Output: ((i8.q1)), (t2.f1) -> Seq Scan on public.text_tbl t3 Output: (i8.q1), t2.f1 -(30 rows) +(32 rows) select * from text_tbl t1 @@ -5225,8 +5229,8 @@ select 1 from left join text_tbl as tt4 on (tt3.f1 = tt4.f1), lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 where tt1.f1 = ss1.c0; - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Nested Loop Output: 1 -> Nested Loop Left Join @@ -5252,6 +5256,7 @@ where tt1.f1 = ss1.c0; Output: ss1.c0 Cache Key: tt4.f1 Cache Mode: binary + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 -> Subquery Scan on ss1 Output: ss1.c0 Filter: (ss1.c0 = 'foo'::text) @@ -5259,7 +5264,7 @@ where tt1.f1 = ss1.c0; Output: (tt4.f1) -> Seq Scan on public.text_tbl tt5 Output: tt4.f1 -(32 rows) +(33 rows) select 1 from text_tbl as tt1 @@ -6462,17 +6467,18 @@ select * from sj t1 join lateral (select * from sj tablesample system(t1.b)) s on t1.a = s.a; - QUERY PLAN ---------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Nested Loop -> Seq Scan on sj t1 -> Memoize Cache Key: t1.a, t1.b Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Sample Scan on sj Sampling: system (t1.b) Filter: (t1.a = a) -(8 rows) +(9 rows) -- Ensure that SJE does not form a self-referential lateral dependency explain (costs off) @@ -7614,43 +7620,46 @@ select count(*) from tenk1 a, lateral generate_series(1,two) g; explain (costs off) select count(*) from tenk1 a, lateral generate_series(1,two) g; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Aggregate -> Nested Loop -> Seq Scan on tenk1 a -> Memoize Cache Key: a.two Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Function Scan on generate_series g -(7 rows) +(8 rows) explain (costs off) select count(*) from tenk1 a cross join lateral generate_series(1,two) g; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Aggregate -> Nested Loop -> Seq Scan on tenk1 a -> Memoize Cache Key: a.two Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Function Scan on generate_series g -(7 rows) +(8 rows) -- don't need the explicit LATERAL keyword for functions explain (costs off) select count(*) from tenk1 a, generate_series(1,two) g; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Aggregate -> Nested Loop -> Seq Scan on tenk1 a -> Memoize Cache Key: a.two Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Function Scan on generate_series g -(7 rows) +(8 rows) -- lateral with UNION ALL subselect explain (costs off) @@ -7702,8 +7711,8 @@ select count(*) from tenk1 a, explain (costs off) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Aggregate -> Nested Loop -> Nested Loop @@ -7712,9 +7721,10 @@ explain (costs off) -> Memoize Cache Key: "*VALUES*".column1 Cache Mode: logical + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 = "*VALUES*".column1) -(10 rows) +(11 rows) select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 38dfaf021c9..39c76aaa1a4 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -46,12 +46,13 @@ WHERE t2.unique1 < 1000;', false); -> Memoize (actual rows=1.00 loops=N) Cache Key: t2.twenty Cache Mode: logical + Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20 Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N) Index Cond: (unique1 = t2.twenty) Heap Fetches: N Index Searches: N -(13 rows) +(14 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -78,12 +79,13 @@ WHERE t1.unique1 < 1000;', false); -> Memoize (actual rows=1.00 loops=N) Cache Key: t1.twenty Cache Mode: binary + Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20 Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N) Index Cond: (unique1 = t1.twenty) Heap Fetches: N Index Searches: N -(13 rows) +(14 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -113,6 +115,7 @@ WHERE t1.unique1 < 10;', false); -> Memoize (actual rows=2.00 loops=N) Cache Key: t1.two Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 Hits: 8 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Subquery Scan on t2 (actual rows=2.00 loops=N) Filter: (t1.two = t2.two) @@ -120,7 +123,7 @@ WHERE t1.unique1 < 10;', false); -> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N) Index Cond: (unique1 < 4) Index Searches: N -(15 rows) +(16 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN @@ -149,13 +152,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false); -> Memoize (actual rows=1.00 loops=N) Cache Key: (t1.two + 1) Cache Mode: binary + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N) Filter: ((t1.two + 1) = unique1) Rows Removed by Filter: 9999 Heap Fetches: N Index Searches: N -(14 rows) +(15 rows) -- And check we get the expected results. SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN @@ -182,11 +186,12 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false); -> Memoize (actual rows=1.00 loops=N) Cache Key: t1.two, t1.twenty Cache Mode: binary + Estimated Capacity: 40 Estimated Distinct Lookup Keys: 40 Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Seq Scan on tenk1 t2 (actual rows=1.00 loops=N) Filter: ((t1.twenty = unique1) AND (t1.two = two)) Rows Removed by Filter: 9999 -(12 rows) +(13 rows) -- And check we get the expected results. SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN @@ -220,13 +225,14 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); -> Memoize (actual rows=2.00 loops=N) Cache Key: t1.x, (t1.t)::numeric Cache Mode: logical + Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20 Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2.00 loops=N) Index Cond: (x = (t1.t)::numeric) Filter: (t1.x = (t)::numeric) Heap Fetches: N Index Searches: N -(11 rows) +(12 rows) DROP TABLE expr_key; -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions @@ -249,12 +255,13 @@ WHERE t2.unique1 < 1200;', true); -> Memoize (actual rows=1.00 loops=N) Cache Key: t2.thousand Cache Mode: logical + Estimated Capacity: 655 Estimated Distinct Lookup Keys: 721 Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N) Index Cond: (unique1 = t2.thousand) Heap Fetches: N Index Searches: N -(13 rows) +(14 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); @@ -273,12 +280,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false); -> Memoize (actual rows=2.00 loops=N) Cache Key: f1.f Cache Mode: logical + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N) Index Cond: (f = f1.f) Heap Fetches: N Index Searches: N -(12 rows) +(13 rows) -- Ensure memoize operates in binary mode SELECT explain_memoize(' @@ -292,12 +300,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false); -> Memoize (actual rows=2.00 loops=N) Cache Key: f1.f Cache Mode: binary + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N) Index Cond: (f <= f1.f) Heap Fetches: N Index Searches: N -(12 rows) +(13 rows) DROP TABLE flt; -- Exercise Memoize in binary mode with a large fixed width type and a @@ -320,11 +329,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false); -> Memoize (actual rows=4.00 loops=N) Cache Key: s1.n Cache Mode: binary + Estimated Capacity: 3 Estimated Distinct Lookup Keys: 3 Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Scan using strtest_n_idx on strtest s2 (actual rows=4.00 loops=N) Index Cond: (n <= s1.n) Index Searches: N -(10 rows) +(11 rows) -- Ensure we get 3 hits and 3 misses SELECT explain_memoize(' @@ -337,11 +347,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false); -> Memoize (actual rows=4.00 loops=N) Cache Key: s1.t Cache Mode: binary + Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4 Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Scan using strtest_t_idx on strtest s2 (actual rows=4.00 loops=N) Index Cond: (t <= s1.t) Index Searches: N -(10 rows) +(11 rows) DROP TABLE strtest; -- Ensure memoize works with partitionwise join @@ -366,6 +377,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Memoize (actual rows=4.00 loops=N) Cache Key: t1_1.a Cache Mode: logical + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4.00 loops=N) Index Cond: (a = t1_1.a) @@ -378,12 +390,13 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Memoize (actual rows=4.00 loops=N) Cache Key: t1_2.a Cache Mode: logical + Estimated Capacity: 2 Estimated Distinct Lookup Keys: 2 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4.00 loops=N) Index Cond: (a = t1_2.a) Heap Fetches: N Index Searches: N -(25 rows) +(27 rows) -- Ensure memoize works with parameterized union-all Append path SET enable_partitionwise_join TO off; @@ -400,6 +413,7 @@ ON t1.a = t2.a;', false); -> Memoize (actual rows=4.00 loops=N) Cache Key: t1.a Cache Mode: logical + Estimated Capacity: 1 Estimated Distinct Lookup Keys: 1 Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Append (actual rows=4.00 loops=N) -> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N) @@ -410,7 +424,7 @@ ON t1.a = t2.a;', false); Index Cond: (a = t1.a) Heap Fetches: N Index Searches: N -(17 rows) +(18 rows) DROP TABLE prt; RESET enable_partitionwise_join; @@ -424,8 +438,8 @@ WHERE unique1 < 3 SELECT 1 FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.hundred WHERE t0.ten = t1.twenty AND t0.two <> t2.four OFFSET 0); - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------ Index Scan using tenk1_unique1 on tenk1 t0 Index Cond: (unique1 < 3) Filter: EXISTS(SubPlan 1) @@ -436,10 +450,11 @@ WHERE unique1 < 3 -> Memoize Cache Key: t2.hundred Cache Mode: logical + Estimated Capacity: 100 Estimated Distinct Lookup Keys: 100 -> Index Scan using tenk1_unique1 on tenk1 t1 Index Cond: (unique1 = t2.hundred) Filter: (t0.ten = twenty) -(13 rows) +(14 rows) -- Ensure the above query returns the correct result SELECT unique1 FROM tenk1 t0 @@ -469,8 +484,8 @@ EXPLAIN (COSTS OFF) SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2 WHERE t1.unique1 < 1000; - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 @@ -483,9 +498,10 @@ WHERE t1.unique1 < 1000; -> Memoize Cache Key: t1.twenty Cache Mode: logical + Estimated Capacity: 20 Estimated Distinct Lookup Keys: 20 -> Index Only Scan using tenk1_unique1 on tenk1 t2 Index Cond: (unique1 = t1.twenty) -(14 rows) +(15 rows) -- And ensure the parallel plan gives us the correct results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6101c8c7cf1..d3c37ffe22b 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -5289,8 +5289,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1; -- Increase number of tuples requested and an IndexScan will be chosen EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- Limit -> Append -> Nested Loop @@ -5298,6 +5298,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_1.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1 Index Cond: (c = p1_1.c) -> Nested Loop @@ -5305,6 +5306,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_2.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2 Index Cond: (c = p1_2.c) -> Nested Loop @@ -5312,9 +5314,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_3.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3 Index Cond: (c = p1_3.c) -(23 rows) +(26 rows) -- If almost all the data should be fetched - prefer SeqScan EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000; @@ -5343,8 +5346,8 @@ SET max_parallel_workers_per_gather = 1; SET debug_parallel_query = on; -- Partial paths should also be smart enough to employ limits EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------- Gather Workers Planned: 1 Single Copy: true @@ -5355,6 +5358,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_1.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1 Index Cond: (c = p1_1.c) -> Nested Loop @@ -5362,6 +5366,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_2.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2 Index Cond: (c = p1_2.c) -> Nested Loop @@ -5369,9 +5374,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100; -> Memoize Cache Key: p1_3.c Cache Mode: logical + Estimated Capacity: 12 Estimated Distinct Lookup Keys: 12 -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3 Index Cond: (c = p1_3.c) -(26 rows) +(29 rows) RESET debug_parallel_query; -- Remove indexes from the partitioned table and its partitions diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 40d8056fcea..f5575c7d332 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1316,8 +1316,8 @@ select sum(o.four), sum(ss.a) from select * from x ) ss where o.ten = 1; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Aggregate -> Nested Loop -> Seq Scan on onek o @@ -1325,13 +1325,14 @@ where o.ten = 1; -> Memoize Cache Key: o.four Cache Mode: binary + Estimated Capacity: 4 Estimated Distinct Lookup Keys: 4 -> CTE Scan on x CTE x -> Recursive Union -> Result -> WorkTable Scan on x x_1 Filter: (a < 10) -(13 rows) +(14 rows) select sum(o.four), sum(ss.a) from onek o cross join lateral ( @@ -2642,6 +2643,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); -> Memoize Cache Key: b.hundred, b.odd Cache Mode: binary + Estimated Capacity: 1000 Estimated Distinct Lookup Keys: 1000 -> Subquery Scan on "ANY_subquery" Filter: (b.hundred = "ANY_subquery".min) -> Result @@ -2650,7 +2652,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); -> Index Scan using tenk2_hundred on tenk2 c Index Cond: (hundred IS NOT NULL) Filter: (odd = b.odd) -(16 rows) +(17 rows) -- -- Test VALUES to ARRAY (VtA) transformation -- 2.34.1