Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite4deae7

Browse files
committed
Fix handling of NULLs in MCV items and constants
There were two issues in how the extended statistics handled NULL valuesin opclauses. Firstly, the code was oblivious to the possibility thatConst may be NULL (constisnull=true) in which case the constvalue isundefined. We need to treat this as a mismatch, and not call the proc.Secondly, the MCV item itself may contain NULL values too - the codealready did check that, and updated the match bitmap accordingly, butfailed to ensure we won't call the operator procedure anyway. It didwork for AND-clauses, because in that case false in the bitmap stopsevaluation of further clauses. But for OR-clauses ir was not easy toget incorrect estimates or even trigger a crash.This fixes both issues by extending the existing check so that it looksat constisnull too, and making sure it skips calling the procedure.Discussion:https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu
1 parente8b6ae2 commite4deae7

File tree

3 files changed

+60
-5
lines changed

3 files changed

+60
-5
lines changed

‎src/backend/statistics/mcv.c

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1593,12 +1593,18 @@ mcv_get_match_bitmap(PlannerInfo *root, List *clauses,
15931593
MCVItem*item=&mcvlist->items[i];
15941594

15951595
/*
1596-
*For AND-lists, we can also markNULLitems as 'no
1597-
*match' (and then skip them). For OR-lists this is not
1598-
*possible.
1596+
*When the MCV item or the Const value isNULLwe can treat
1597+
*this as a mismatch. We must not call the operator because
1598+
*of strictness.
15991599
*/
1600-
if ((!is_or)&&item->isnull[idx])
1601-
matches[i]= false;
1600+
if (item->isnull[idx]||cst->constisnull)
1601+
{
1602+
/* we only care about AND, because OR can't change */
1603+
if (!is_or)
1604+
matches[i]= false;
1605+
1606+
continue;
1607+
}
16021608

16031609
/* skip MCV items that were already ruled out */
16041610
if ((!is_or)&& (matches[i]== false))

‎src/test/regress/expected/stats_ext.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -619,6 +619,32 @@ SELECT m.*
619619
0 | {1,2,3} | {f,f,f} | 1 | 1
620620
(1 row)
621621

622+
-- 2 distinct combinations with NULL values, all in the MCV list
623+
TRUNCATE mcv_lists;
624+
DROP STATISTICS mcv_lists_stats;
625+
INSERT INTO mcv_lists (a, b, c, d)
626+
SELECT
627+
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
628+
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END),
629+
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
630+
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
631+
FROM generate_series(1,5000) s(i);
632+
ANALYZE mcv_lists;
633+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
634+
estimated | actual
635+
-----------+--------
636+
3750 | 2500
637+
(1 row)
638+
639+
-- create statistics
640+
CREATE STATISTICS mcv_lists_stats (mcv) ON b, d FROM mcv_lists;
641+
ANALYZE mcv_lists;
642+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
643+
estimated | actual
644+
-----------+--------
645+
2500 | 2500
646+
(1 row)
647+
622648
-- mcv with arrays
623649
CREATE TABLE mcv_lists_arrays (
624650
a TEXT[],

‎src/test/regress/sql/stats_ext.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -393,6 +393,29 @@ SELECT m.*
393393
WHEREs.stxname='mcv_lists_stats'
394394
ANDd.stxoid=s.oid;
395395

396+
-- 2 distinct combinations with NULL values, all in the MCV list
397+
TRUNCATE mcv_lists;
398+
DROP STATISTICS mcv_lists_stats;
399+
400+
INSERT INTO mcv_lists (a, b, c, d)
401+
SELECT
402+
(CASE WHEN mod(i,2)=0 THENNULL ELSE0 END),
403+
(CASE WHEN mod(i,2)=0 THENNULL ELSE'x' END),
404+
(CASE WHEN mod(i,2)=0 THENNULL ELSE0 END),
405+
(CASE WHEN mod(i,2)=0 THENNULL ELSE'x' END)
406+
FROM generate_series(1,5000) s(i);
407+
408+
ANALYZE mcv_lists;
409+
410+
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b =''x'' OR d =''x''');
411+
412+
-- create statistics
413+
CREATE STATISTICS mcv_lists_stats (mcv)ON b, dFROM mcv_lists;
414+
415+
ANALYZE mcv_lists;
416+
417+
SELECT*FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b =''x'' OR d =''x''');
418+
396419
-- mcv with arrays
397420
CREATETABLEmcv_lists_arrays (
398421
aTEXT[],

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp