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

Commitd8cfa82

Browse files
committed
Improve test coverage for multi-column MCV lists
The regression tests for extended statistics were not testing a coupleof important cases for the MCV lists: * IS NOT NULL clauses - We did have queries with IS NULL clauses, but not the negative case. * clauses with variable on the right - All the clauses had the Var on the left, i.e. (Var op Const), so this adds (Const op Var) too. * columns with fixed-length types passed by reference - All columns were using either by-value or varlena types, so add a test with UUID columns too. This matters for (de)serialization. * NULL-only dimension - When one of the columns contains only NULL values, we treat it a a special case during (de)serialization. * arrays containing NULL - When the constant parameter contains NULL value, we need to handle it correctly during estimation, for all IN, ANY and ALL clauses.Discussion:https://www.postgresql.org/message-id/flat/20200113230008.g67iyk4cs3xbnjju@developmentAuthor: Tomas Vondra
1 parentf969678 commitd8cfa82

File tree

2 files changed

+335
-4
lines changed

2 files changed

+335
-4
lines changed

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

Lines changed: 235 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -816,18 +816,36 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
816816
1 | 50
817817
(1 row)
818818

819+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = a AND ''1'' = b');
820+
estimated | actual
821+
-----------+--------
822+
1 | 50
823+
(1 row)
824+
819825
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 1 AND b < ''1''');
820826
estimated | actual
821827
-----------+--------
822828
1 | 50
823829
(1 row)
824830

831+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > a AND ''1'' > b');
832+
estimated | actual
833+
-----------+--------
834+
1 | 50
835+
(1 row)
836+
825837
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 0 AND b <= ''0''');
826838
estimated | actual
827839
-----------+--------
828840
1 | 50
829841
(1 row)
830842

843+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 0 >= a AND ''0'' >= b');
844+
estimated | actual
845+
-----------+--------
846+
1 | 50
847+
(1 row)
848+
831849
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
832850
estimated | actual
833851
-----------+--------
@@ -840,12 +858,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND b <
840858
1 | 50
841859
(1 row)
842860

861+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND ''1'' > b AND 5 > c');
862+
estimated | actual
863+
-----------+--------
864+
1 | 50
865+
(1 row)
866+
843867
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 4 AND b <= ''0'' AND c <= 4');
844868
estimated | actual
845869
-----------+--------
846870
1 | 50
847871
(1 row)
848872

873+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 4 >= a AND ''0'' >= b AND 4 >= c');
874+
estimated | actual
875+
-----------+--------
876+
1 | 50
877+
(1 row)
878+
849879
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1');
850880
estimated | actual
851881
-----------+--------
@@ -864,30 +894,60 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51
864894
8 | 200
865895
(1 row)
866896

897+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)');
898+
estimated | actual
899+
-----------+--------
900+
8 | 200
901+
(1 row)
902+
867903
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
868904
estimated | actual
869905
-----------+--------
870906
8 | 200
871907
(1 row)
872908

909+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[NULL, 1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2'', NULL])');
910+
estimated | actual
911+
-----------+--------
912+
8 | 200
913+
(1 row)
914+
873915
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')');
874916
estimated | actual
875917
-----------+--------
876918
26 | 150
877919
(1 row)
878920

921+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')');
922+
estimated | actual
923+
-----------+--------
924+
26 | 150
925+
(1 row)
926+
879927
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])');
880928
estimated | actual
881929
-----------+--------
882930
10 | 100
883931
(1 row)
884932

933+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])');
934+
estimated | actual
935+
-----------+--------
936+
10 | 100
937+
(1 row)
938+
885939
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', ''3'') AND c > ANY (ARRAY[1, 2, 3])');
886940
estimated | actual
887941
-----------+--------
888942
1 | 100
889943
(1 row)
890944

945+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', NULL, ''3'') AND c > ANY (ARRAY[1, 2, NULL, 3])');
946+
estimated | actual
947+
-----------+--------
948+
1 | 100
949+
(1 row)
950+
891951
-- create statistics
892952
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
893953
ANALYZE mcv_lists;
@@ -897,18 +957,36 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
897957
50 | 50
898958
(1 row)
899959

960+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = a AND ''1'' = b');
961+
estimated | actual
962+
-----------+--------
963+
50 | 50
964+
(1 row)
965+
900966
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 1 AND b < ''1''');
901967
estimated | actual
902968
-----------+--------
903969
50 | 50
904970
(1 row)
905971

972+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > a AND ''1'' > b');
973+
estimated | actual
974+
-----------+--------
975+
50 | 50
976+
(1 row)
977+
906978
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 0 AND b <= ''0''');
907979
estimated | actual
908980
-----------+--------
909981
50 | 50
910982
(1 row)
911983

984+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 0 >= a AND ''0'' >= b');
985+
estimated | actual
986+
-----------+--------
987+
50 | 50
988+
(1 row)
989+
912990
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
913991
estimated | actual
914992
-----------+--------
@@ -921,12 +999,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND b <
921999
50 | 50
9221000
(1 row)
9231001

1002+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND ''1'' > b AND 5 > c');
1003+
estimated | actual
1004+
-----------+--------
1005+
50 | 50
1006+
(1 row)
1007+
9241008
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 4 AND b <= ''0'' AND c <= 4');
9251009
estimated | actual
9261010
-----------+--------
9271011
50 | 50
9281012
(1 row)
9291013

1014+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 4 >= a AND ''0'' >= b AND 4 >= c');
1015+
estimated | actual
1016+
-----------+--------
1017+
50 | 50
1018+
(1 row)
1019+
9301020
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1');
9311021
estimated | actual
9321022
-----------+--------
@@ -939,30 +1029,60 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51
9391029
200 | 200
9401030
(1 row)
9411031

1032+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)');
1033+
estimated | actual
1034+
-----------+--------
1035+
200 | 200
1036+
(1 row)
1037+
9421038
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
9431039
estimated | actual
9441040
-----------+--------
9451041
200 | 200
9461042
(1 row)
9471043

1044+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[NULL, 1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2'', NULL])');
1045+
estimated | actual
1046+
-----------+--------
1047+
200 | 200
1048+
(1 row)
1049+
9481050
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')');
9491051
estimated | actual
9501052
-----------+--------
9511053
150 | 150
9521054
(1 row)
9531055

1056+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')');
1057+
estimated | actual
1058+
-----------+--------
1059+
150 | 150
1060+
(1 row)
1061+
9541062
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])');
9551063
estimated | actual
9561064
-----------+--------
9571065
100 | 100
9581066
(1 row)
9591067

1068+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])');
1069+
estimated | actual
1070+
-----------+--------
1071+
100 | 100
1072+
(1 row)
1073+
9601074
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', ''3'') AND c > ANY (ARRAY[1, 2, 3])');
9611075
estimated | actual
9621076
-----------+--------
9631077
100 | 100
9641078
(1 row)
9651079

1080+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND b IN (''1'', ''2'', NULL, ''3'') AND c > ANY (ARRAY[1, 2, NULL, 3])');
1081+
estimated | actual
1082+
-----------+--------
1083+
100 | 100
1084+
(1 row)
1085+
9661086
-- we can't use the statistic for OR clauses that are not fully covered (missing 'd' attribute)
9671087
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL');
9681088
estimated | actual
@@ -1019,6 +1139,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND
10191139
1 | 50
10201140
(1 row)
10211141

1142+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NOT NULL');
1143+
estimated | actual
1144+
-----------+--------
1145+
49 | 0
1146+
(1 row)
1147+
1148+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NOT NULL AND b IS NULL AND c IS NOT NULL');
1149+
estimated | actual
1150+
-----------+--------
1151+
95 | 0
1152+
(1 row)
1153+
1154+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AND b IN (''0'', ''1'')');
1155+
estimated | actual
1156+
-----------+--------
1157+
1 | 50
1158+
(1 row)
1159+
10221160
-- create statistics
10231161
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
10241162
ANALYZE mcv_lists;
@@ -1034,6 +1172,24 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND
10341172
50 | 50
10351173
(1 row)
10361174

1175+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NOT NULL');
1176+
estimated | actual
1177+
-----------+--------
1178+
1 | 0
1179+
(1 row)
1180+
1181+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NOT NULL AND b IS NULL AND c IS NOT NULL');
1182+
estimated | actual
1183+
-----------+--------
1184+
1 | 0
1185+
(1 row)
1186+
1187+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AND b IN (''0'', ''1'')');
1188+
estimated | actual
1189+
-----------+--------
1190+
50 | 50
1191+
(1 row)
1192+
10371193
-- test pg_mcv_list_items with a very simple (single item) MCV list
10381194
TRUNCATE mcv_lists;
10391195
INSERT INTO mcv_lists (a, b, c) SELECT 1, 2, 3 FROM generate_series(1,1000) s(i);
@@ -1053,7 +1209,7 @@ TRUNCATE mcv_lists;
10531209
DROP STATISTICS mcv_lists_stats;
10541210
INSERT INTO mcv_lists (a, b, c, d)
10551211
SELECT
1056-
(CASE WHEN mod(i,2) = 0 THENNULL ELSE 0 END),
1212+
NULL, -- alwaysNULL
10571213
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END),
10581214
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
10591215
(CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
@@ -1065,15 +1221,92 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d
10651221
3750 | 2500
10661222
(1 row)
10671223

1224+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''x'' OR d = ''x''');
1225+
estimated | actual
1226+
-----------+--------
1227+
3750 | 2500
1228+
(1 row)
1229+
1230+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')');
1231+
estimated | actual
1232+
-----------+--------
1233+
3750 | 2500
1234+
(1 row)
1235+
10681236
-- create statistics
1069-
CREATE STATISTICS mcv_lists_stats (mcv) ON b, d FROM mcv_lists;
1237+
CREATE STATISTICS mcv_lists_stats (mcv) ONa,b, d FROM mcv_lists;
10701238
ANALYZE mcv_lists;
1239+
-- test pg_mcv_list_items with MCV list containing variable-length data and NULLs
1240+
SELECT m.*
1241+
FROM pg_statistic_ext s, pg_statistic_ext_data d,
1242+
pg_mcv_list_items(d.stxdmcv) m
1243+
WHERE s.stxname = 'mcv_lists_stats'
1244+
AND d.stxoid = s.oid;
1245+
index | values | nulls | frequency | base_frequency
1246+
-------+------------------+---------+-----------+----------------
1247+
0 | {NULL,x,x} | {t,f,f} | 0.5 | 0.25
1248+
1 | {NULL,NULL,NULL} | {t,t,t} | 0.5 | 0.25
1249+
(2 rows)
1250+
10711251
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
10721252
estimated | actual
10731253
-----------+--------
10741254
2500 | 2500
10751255
(1 row)
10761256

1257+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''x'' OR d = ''x''');
1258+
estimated | actual
1259+
-----------+--------
1260+
2500 | 2500
1261+
(1 row)
1262+
1263+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')');
1264+
estimated | actual
1265+
-----------+--------
1266+
2500 | 2500
1267+
(1 row)
1268+
1269+
-- mcv with pass-by-ref fixlen types, e.g. uuid
1270+
CREATE TABLE mcv_lists_uuid (
1271+
a UUID,
1272+
b UUID,
1273+
c UUID
1274+
);
1275+
INSERT INTO mcv_lists_uuid (a, b, c)
1276+
SELECT
1277+
md5(mod(i,100)::text)::uuid,
1278+
md5(mod(i,50)::text)::uuid,
1279+
md5(mod(i,25)::text)::uuid
1280+
FROM generate_series(1,5000) s(i);
1281+
ANALYZE mcv_lists_uuid;
1282+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1283+
estimated | actual
1284+
-----------+--------
1285+
1 | 50
1286+
(1 row)
1287+
1288+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1289+
estimated | actual
1290+
-----------+--------
1291+
1 | 50
1292+
(1 row)
1293+
1294+
CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
1295+
FROM mcv_lists_uuid;
1296+
ANALYZE mcv_lists_uuid;
1297+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1298+
estimated | actual
1299+
-----------+--------
1300+
50 | 50
1301+
(1 row)
1302+
1303+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND c = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1304+
estimated | actual
1305+
-----------+--------
1306+
50 | 50
1307+
(1 row)
1308+
1309+
DROP TABLE mcv_lists_uuid;
10771310
-- mcv with arrays
10781311
CREATE TABLE mcv_lists_arrays (
10791312
a TEXT[],

0 commit comments

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