diff options
| -rw-r--r-- | src/backend/statistics/mcv.c | 16 | ||||
| -rw-r--r-- | src/test/regress/expected/stats_ext.out | 26 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_ext.sql | 23 |
3 files changed, 60 insertions, 5 deletions
diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index a708a8f6740..429cafd689b 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -1593,12 +1593,18 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses, MCVItem *item = &mcvlist->items[i]; /* - * For AND-lists, we can also mark NULL items as 'no - * match' (and then skip them). For OR-lists this is not - * possible. + * When the MCV item or the Const value is NULL we can treat + * this as a mismatch. We must not call the operator because + * of strictness. */ - if ((!is_or) && item->isnull[idx]) - matches[i] = false; + if (item->isnull[idx] || cst->constisnull) + { + /* we only care about AND, because OR can't change */ + if (!is_or) + matches[i] = false; + + continue; + } /* skip MCV items that were already ruled out */ if ((!is_or) && (matches[i] == false)) diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 6a070a9649d..94b8a8f8b85 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -619,6 +619,32 @@ SELECT m.* 0 | {1,2,3} | {f,f,f} | 1 | 1 (1 row) +-- 2 distinct combinations with NULL values, all in the MCV list +TRUNCATE mcv_lists; +DROP STATISTICS mcv_lists_stats; +INSERT INTO mcv_lists (a, b, c, d) + SELECT + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END) + FROM generate_series(1,5000) s(i); +ANALYZE mcv_lists; +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x'''); + estimated | actual +-----------+-------- + 3750 | 2500 +(1 row) + +-- create statistics +CREATE STATISTICS mcv_lists_stats (mcv) ON b, d FROM mcv_lists; +ANALYZE mcv_lists; +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x'''); + estimated | actual +-----------+-------- + 2500 | 2500 +(1 row) + -- mcv with arrays CREATE TABLE mcv_lists_arrays ( a TEXT[], diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 7a77d20446e..4bc1536727b 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -393,6 +393,29 @@ SELECT m.* WHERE s.stxname = 'mcv_lists_stats' AND d.stxoid = s.oid; +-- 2 distinct combinations with NULL values, all in the MCV list +TRUNCATE mcv_lists; +DROP STATISTICS mcv_lists_stats; + +INSERT INTO mcv_lists (a, b, c, d) + SELECT + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END), + (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END) + FROM generate_series(1,5000) s(i); + +ANALYZE mcv_lists; + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x'''); + +-- create statistics +CREATE STATISTICS mcv_lists_stats (mcv) ON b, d FROM mcv_lists; + +ANALYZE mcv_lists; + +SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x'''); + -- mcv with arrays CREATE TABLE mcv_lists_arrays ( a TEXT[], |