From f4857082bc16f36a5cdc6f8328cdc8071bb1715e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 1 Nov 2022 14:34:44 -0400 Subject: [PATCH] Fix planner failure with extended statistics on partitioned tables. Some cases would result in "cache lookup failed for statistics object", due to trying to fetch inherited statistics when only non-inherited ones are available or vice versa. Richard Guo and Justin Pryzby Discussion: https://postgr.es/m/20221030170520.GM16921@telsasoft.com --- src/backend/utils/adt/selfuncs.c | 11 +++++++++-- src/test/regress/expected/stats_ext.out | 8 +++++++- src/test/regress/sql/stats_ext.sql | 3 ++- 3 files changed, 18 insertions(+), 4 deletions(-) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 69e0fb98f5b..d597b7e81fc 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -3913,7 +3913,7 @@ estimate_multivariate_ndistinct(PlannerInfo *root, RelOptInfo *rel, Oid statOid = InvalidOid; MVNDistinct *stats; StatisticExtInfo *matched_info = NULL; - RangeTblEntry *rte; + RangeTblEntry *rte = planner_rt_fetch(rel->relid, root); /* bail out immediately if the table has no extended statistics */ if (!rel->statlist) @@ -3933,6 +3933,10 @@ estimate_multivariate_ndistinct(PlannerInfo *root, RelOptInfo *rel, if (info->kind != STATS_EXT_NDISTINCT) continue; + /* skip statistics with mismatching stxdinherit value */ + if (info->inherit != rte->inh) + continue; + /* * Determine how many expressions (and variables in non-matched * expressions) match. We'll then use these numbers to pick the @@ -4004,7 +4008,6 @@ estimate_multivariate_ndistinct(PlannerInfo *root, RelOptInfo *rel, Assert(nmatches_vars + nmatches_exprs > 1); - rte = planner_rt_fetch(rel->relid, root); stats = statext_ndistinct_load(statOid, rte->inh); /* @@ -5241,6 +5244,10 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, if (info->kind != STATS_EXT_EXPRESSIONS) continue; + /* skip stats with mismatching stxdinherit value */ + if (info->inherit != rte->inh) + continue; + pos = 0; foreach(expr_item, info->exprs) { diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index a2bc409e06f..03880874c17 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -232,7 +232,7 @@ DROP TABLE stxdinh, stxdinh1, stxdinh2; CREATE TABLE stxdinp(i int, a int, b int) PARTITION BY RANGE (i); CREATE TABLE stxdinp1 PARTITION OF stxdinp FOR VALUES FROM (1) TO (100); INSERT INTO stxdinp SELECT 1, a/100, a/100 FROM generate_series(1, 999) a; -CREATE STATISTICS stxdinp ON a, b FROM stxdinp; +CREATE STATISTICS stxdinp ON (a + 1), a, b FROM stxdinp; VACUUM ANALYZE stxdinp; -- partitions are processed recursively SELECT 1 FROM pg_statistic_ext WHERE stxrelid = 'stxdinp'::regclass; ?column? @@ -246,6 +246,12 @@ SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinp GROUP BY 1, 2'); 10 | 10 (1 row) +SELECT * FROM check_estimated_rows('SELECT a + 1, b FROM ONLY stxdinp GROUP BY 1, 2'); + estimated | actual +-----------+-------- + 1 | 0 +(1 row) + DROP TABLE stxdinp; -- basic test for statistics on expressions CREATE TABLE ab1 (a INTEGER, b INTEGER, c TIMESTAMP, d TIMESTAMPTZ); diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 19417561bd6..d0d42cd0136 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -139,10 +139,11 @@ DROP TABLE stxdinh, stxdinh1, stxdinh2; CREATE TABLE stxdinp(i int, a int, b int) PARTITION BY RANGE (i); CREATE TABLE stxdinp1 PARTITION OF stxdinp FOR VALUES FROM (1) TO (100); INSERT INTO stxdinp SELECT 1, a/100, a/100 FROM generate_series(1, 999) a; -CREATE STATISTICS stxdinp ON a, b FROM stxdinp; +CREATE STATISTICS stxdinp ON (a + 1), a, b FROM stxdinp; VACUUM ANALYZE stxdinp; -- partitions are processed recursively SELECT 1 FROM pg_statistic_ext WHERE stxrelid = 'stxdinp'::regclass; SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinp GROUP BY 1, 2'); +SELECT * FROM check_estimated_rows('SELECT a + 1, b FROM ONLY stxdinp GROUP BY 1, 2'); DROP TABLE stxdinp; -- basic test for statistics on expressions -- 2.30.2