src/test/regress/sql/stats_ext.sql

   1 -- Generic extended statistics support
   2
   3 --
   4 -- Note: tables for which we check estimated row counts should be created
   5 -- with autovacuum_enabled = off, so that we don't have unstable results
   6 -- from auto-analyze happening when we didn't expect it.
   7 --
   8
   9 -- check the number of estimated/actual rows in the top node
  10 create function check_estimated_rows(text) returns table (estimated int, actual int)
  11 language plpgsql as
  12 $$
  13 declare
  14     ln text;
  15     tmp text[];
  16     first_row bool := true;
  17 begin
  18     for ln in
  19         execute format('explain analyze %s', $1)
  20     loop
  21         if first_row then
  22             first_row := false;
  23             tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
  24             return query select tmp[1]::int, tmp[2]::int;
  25         end if;
  26     end loop;
  27 end;
  28 $$;
  29
  30 -- Verify failures
  31 CREATE TABLE ext_stats_test (x text, y int, z int);
  32 CREATE STATISTICS tst;
  33 CREATE STATISTICS tst ON a, b;
  34 CREATE STATISTICS tst FROM sometab;
  35 CREATE STATISTICS tst ON a, b FROM nonexistent;
  36 CREATE STATISTICS tst ON a, b FROM ext_stats_test;
  37 CREATE STATISTICS tst ON x, x, y FROM ext_stats_test;
  38 CREATE STATISTICS tst ON x, x, y, x, x, y, x, x, y FROM ext_stats_test;
  39 CREATE STATISTICS tst ON x, x, y, x, x, (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1) FROM ext_stats_test;
  40 CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1) FROM ext_stats_test;
  41 CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test;
  42 CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
  43 -- incorrect expressions
  44 CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
  45 CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses
  46 CREATE STATISTICS tst ON (x, y) FROM ext_stats_test; -- tuple expression
  47 DROP TABLE ext_stats_test;
  48
  49 -- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
  50 CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
  51 CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
  52 COMMENT ON STATISTICS ab1_a_b_stats IS 'new comment';
  53 CREATE ROLE regress_stats_ext;
  54 SET SESSION AUTHORIZATION regress_stats_ext;
  55 COMMENT ON STATISTICS ab1_a_b_stats IS 'changed comment';
  56 DROP STATISTICS ab1_a_b_stats;
  57 ALTER STATISTICS ab1_a_b_stats RENAME TO ab1_a_b_stats_new;
  58 RESET SESSION AUTHORIZATION;
  59 DROP ROLE regress_stats_ext;
  60
  61 CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
  62 DROP STATISTICS ab1_a_b_stats;
  63
  64 CREATE SCHEMA regress_schema_2;
  65 CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
  66
  67 -- Let's also verify the pg_get_statisticsobjdef output looks sane.
  68 SELECT pg_get_statisticsobjdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a_b_stats';
  69
  70 DROP STATISTICS regress_schema_2.ab1_a_b_stats;
  71
  72 -- Ensure statistics are dropped when columns are
  73 CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
  74 CREATE STATISTICS ab1_a_b_c_stats ON a, b, c FROM ab1;
  75 CREATE STATISTICS ab1_b_a_stats ON b, a FROM ab1;
  76 ALTER TABLE ab1 DROP COLUMN a;
  77 \d ab1
  78 -- Ensure statistics are dropped when table is
  79 SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
  80 DROP TABLE ab1;
  81 SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
  82
  83 -- Ensure things work sanely with SET STATISTICS 0
  84 CREATE TABLE ab1 (a INTEGER, b INTEGER);
  85 ALTER TABLE ab1 ALTER a SET STATISTICS 0;
  86 INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
  87 CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
  88 ANALYZE ab1;
  89 ALTER TABLE ab1 ALTER a SET STATISTICS -1;
  90 -- setting statistics target 0 skips the statistics, without printing any message, so check catalog
  91 ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
  92 \d ab1
  93 ANALYZE ab1;
  94 SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
  95   FROM pg_statistic_ext s, pg_statistic_ext_data d
  96  WHERE s.stxname = 'ab1_a_b_stats'
  97    AND d.stxoid = s.oid;
  98 ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
  99 \d+ ab1
 100 -- partial analyze doesn't build stats either
 101 ANALYZE ab1 (a);
 102 ANALYZE ab1;
 103 DROP TABLE ab1;
 104 ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
 105 ALTER STATISTICS IF EXISTS ab1_a_b_stats SET STATISTICS 0;
 106
 107 -- Ensure we can build statistics for tables with inheritance.
 108 CREATE TABLE ab1 (a INTEGER, b INTEGER);
 109 CREATE TABLE ab1c () INHERITS (ab1);
 110 INSERT INTO ab1 VALUES (1,1);
 111 CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
 112 ANALYZE ab1;
 113 DROP TABLE ab1 CASCADE;
 114
 115 -- Tests for stats with inheritance
 116 CREATE TABLE stxdinh(a int, b int);
 117 CREATE TABLE stxdinh1() INHERITS(stxdinh);
 118 CREATE TABLE stxdinh2() INHERITS(stxdinh);
 119 INSERT INTO stxdinh SELECT mod(a,50), mod(a,100) FROM generate_series(0, 1999) a;
 120 INSERT INTO stxdinh1 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a;
 121 INSERT INTO stxdinh2 SELECT mod(a,100), mod(a,100) FROM generate_series(0, 999) a;
 122 VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2;
 123 -- Ensure non-inherited stats are not applied to inherited query
 124 -- Without stats object, it looks like this
 125 SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2');
 126 SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0');
 127 CREATE STATISTICS stxdinh ON a, b FROM stxdinh;
 128 VACUUM ANALYZE stxdinh, stxdinh1, stxdinh2;
 129 -- Since the stats object does not include inherited stats, it should not
 130 -- affect the estimates
 131 SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* GROUP BY 1, 2');
 132 -- Dependencies are applied at individual relations (within append), so
 133 -- this estimate changes a bit because we improve estimates for the parent
 134 SELECT * FROM check_estimated_rows('SELECT a, b FROM stxdinh* WHERE a = 0 AND b = 0');
 135 DROP TABLE stxdinh, stxdinh1, stxdinh2;
 136
 137 -- basic test for statistics on expressions
 138 CREATE TABLE ab1 (a INTEGER, b INTEGER, c TIMESTAMP, d TIMESTAMPTZ);
 139
 140 -- expression stats may be built on a single expression column
 141 CREATE STATISTICS ab1_exprstat_1 ON (a+b) FROM ab1;
 142
 143 -- with a single expression, we only enable expression statistics
 144 CREATE STATISTICS ab1_exprstat_2 ON (a+b) FROM ab1;
 145 SELECT stxkind FROM pg_statistic_ext WHERE stxname = 'ab1_exprstat_2';
 146
 147 -- adding anything to the expression builds all statistics kinds
 148 CREATE STATISTICS ab1_exprstat_3 ON (a+b), a FROM ab1;
 149 SELECT stxkind FROM pg_statistic_ext WHERE stxname = 'ab1_exprstat_3';
 150
 151 -- date_trunc on timestamptz is not immutable, but that should not matter
 152 CREATE STATISTICS ab1_exprstat_4 ON date_trunc('day', d) FROM ab1;
 153
 154 -- date_trunc on timestamp is immutable
 155 CREATE STATISTICS ab1_exprstat_5 ON date_trunc('day', c) FROM ab1;
 156
 157 -- insert some data and run analyze, to test that these cases build properly
 158 INSERT INTO ab1
 159 SELECT
 160     generate_series(1,10),
 161     generate_series(1,10),
 162     generate_series('2020-10-01'::timestamp, '2020-10-10'::timestamp, interval '1 day'),
 163     generate_series('2020-10-01'::timestamptz, '2020-10-10'::timestamptz, interval '1 day');
 164 ANALYZE ab1;
 165 DROP TABLE ab1;
 166
 167 -- Verify supported object types for extended statistics
 168 CREATE schema tststats;
 169
 170 CREATE TABLE tststats.t (a int, b int, c text);
 171 CREATE INDEX ti ON tststats.t (a, b);
 172 CREATE SEQUENCE tststats.s;
 173 CREATE VIEW tststats.v AS SELECT * FROM tststats.t;
 174 CREATE MATERIALIZED VIEW tststats.mv AS SELECT * FROM tststats.t;
 175 CREATE TYPE tststats.ty AS (a int, b int, c text);
 176 CREATE FOREIGN DATA WRAPPER extstats_dummy_fdw;
 177 CREATE SERVER extstats_dummy_srv FOREIGN DATA WRAPPER extstats_dummy_fdw;
 178 CREATE FOREIGN TABLE tststats.f (a int, b int, c text) SERVER extstats_dummy_srv;
 179 CREATE TABLE tststats.pt (a int, b int, c text) PARTITION BY RANGE (a, b);
 180 CREATE TABLE tststats.pt1 PARTITION OF tststats.pt FOR VALUES FROM (-10, -10) TO (10, 10);
 181
 182 CREATE STATISTICS tststats.s1 ON a, b FROM tststats.t;
 183 CREATE STATISTICS tststats.s2 ON a, b FROM tststats.ti;
 184 CREATE STATISTICS tststats.s3 ON a, b FROM tststats.s;
 185 CREATE STATISTICS tststats.s4 ON a, b FROM tststats.v;
 186 CREATE STATISTICS tststats.s5 ON a, b FROM tststats.mv;
 187 CREATE STATISTICS tststats.s6 ON a, b FROM tststats.ty;
 188 CREATE STATISTICS tststats.s7 ON a, b FROM tststats.f;
 189 CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt;
 190 CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1;
 191 DO $$
 192 DECLARE
 193         relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass;
 194 BEGIN
 195         EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname;
 196 EXCEPTION WHEN wrong_object_type THEN
 197         RAISE NOTICE 'stats on toast table not created';
 198 END;
 199 $$;
 200
 201 DROP SCHEMA tststats CASCADE;
 202 DROP FOREIGN DATA WRAPPER extstats_dummy_fdw CASCADE;
 203
 204 -- n-distinct tests
 205 CREATE TABLE ndistinct (
 206     filler1 TEXT,
 207     filler2 NUMERIC,
 208     a INT,
 209     b INT,
 210     filler3 DATE,
 211     c INT,
 212     d INT
 213 )
 214 WITH (autovacuum_enabled = off);
 215
 216 -- over-estimates when using only per-column statistics
 217 INSERT INTO ndistinct (a, b, c, filler1)
 218      SELECT i/100, i/100, i/100, cash_words((i/100)::money)
 219        FROM generate_series(1,1000) s(i);
 220
 221 ANALYZE ndistinct;
 222
 223 -- Group Aggregate, due to over-estimate of the number of groups
 224 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 225
 226 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c');
 227
 228 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
 229
 230 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
 231
 232 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
 233
 234 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (a+1)');
 235
 236 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 237
 238 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 239
 240 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 241
 242 -- correct command
 243 CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
 244
 245 ANALYZE ndistinct;
 246
 247 SELECT s.stxkind, d.stxdndistinct
 248   FROM pg_statistic_ext s, pg_statistic_ext_data d
 249  WHERE s.stxrelid = 'ndistinct'::regclass
 250    AND d.stxoid = s.oid;
 251
 252 -- minor improvement, make sure the ctid does not break the matching
 253 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ctid, a, b');
 254
 255 -- Hash Aggregate, thanks to estimates improved by the statistic
 256 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 257
 258 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c');
 259
 260 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
 261
 262 -- partial improvement (match on attributes)
 263 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (a+1)');
 264
 265 -- expressions - no improvement
 266 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 267
 268 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 269
 270 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 271
 272 -- last two plans keep using Group Aggregate, because 'd' is not covered
 273 -- by the statistic and while it's NULL-only we assume 200 values for it
 274 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
 275
 276 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
 277
 278 TRUNCATE TABLE ndistinct;
 279
 280 -- under-estimates when using only per-column statistics
 281 INSERT INTO ndistinct (a, b, c, filler1)
 282      SELECT mod(i,13), mod(i,17), mod(i,19),
 283             cash_words(mod(i,23)::int::money)
 284        FROM generate_series(1,1000) s(i);
 285
 286 ANALYZE ndistinct;
 287
 288 SELECT s.stxkind, d.stxdndistinct
 289   FROM pg_statistic_ext s, pg_statistic_ext_data d
 290  WHERE s.stxrelid = 'ndistinct'::regclass
 291    AND d.stxoid = s.oid;
 292
 293 -- correct estimates
 294 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 295
 296 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
 297
 298 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
 299
 300 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
 301
 302 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');
 303
 304 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (a+1)');
 305
 306 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 307
 308 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 309
 310 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 311
 312 DROP STATISTICS s10;
 313
 314 SELECT s.stxkind, d.stxdndistinct
 315   FROM pg_statistic_ext s, pg_statistic_ext_data d
 316  WHERE s.stxrelid = 'ndistinct'::regclass
 317    AND d.stxoid = s.oid;
 318
 319 -- dropping the statistics results in under-estimates
 320 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 321
 322 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');
 323
 324 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');
 325
 326 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');
 327
 328 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');
 329
 330 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (a+1)');
 331
 332 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 333
 334 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 335
 336 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 337
 338 -- ndistinct estimates with statistics on expressions
 339 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 340
 341 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 342
 343 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 344
 345 CREATE STATISTICS s10 (ndistinct) ON (a+1), (b+100), (2*c) FROM ndistinct;
 346
 347 ANALYZE ndistinct;
 348
 349 SELECT s.stxkind, d.stxdndistinct
 350   FROM pg_statistic_ext s, pg_statistic_ext_data d
 351  WHERE s.stxrelid = 'ndistinct'::regclass
 352    AND d.stxoid = s.oid;
 353
 354 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
 355
 356 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100), (2*c)');
 357
 358 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (a+1), (b+100)');
 359
 360 DROP STATISTICS s10;
 361
 362 -- a mix of attributes and expressions
 363 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 364
 365 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (2*c)');
 366
 367 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (2*c)');
 368
 369 CREATE STATISTICS s10 (ndistinct) ON a, b, (2*c) FROM ndistinct;
 370
 371 ANALYZE ndistinct;
 372
 373 SELECT s.stxkind, d.stxdndistinct
 374   FROM pg_statistic_ext s, pg_statistic_ext_data d
 375  WHERE s.stxrelid = 'ndistinct'::regclass
 376    AND d.stxoid = s.oid;
 377
 378 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 379
 380 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (2*c)');
 381
 382 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (2*c)');
 383
 384 DROP STATISTICS s10;
 385
 386 -- combination of multiple ndistinct statistics, with/without expressions
 387 TRUNCATE ndistinct;
 388
 389 -- two mostly independent groups of columns
 390 INSERT INTO ndistinct (a, b, c, d)
 391      SELECT mod(i,3), mod(i,9), mod(i,5), mod(i,20)
 392        FROM generate_series(1,1000) s(i);
 393
 394 ANALYZE ndistinct;
 395
 396 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 397
 398 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 399
 400 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 401
 402 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 403
 404 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 405
 406 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 407
 408 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 409
 410 -- basic statistics on both attributes (no expressions)
 411 CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct;
 412
 413 CREATE STATISTICS s12 (ndistinct) ON c, d FROM ndistinct;
 414
 415 ANALYZE ndistinct;
 416
 417 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 418
 419 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 420
 421 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 422
 423 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 424
 425 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 426
 427 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 428
 429 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 430
 431
 432 -- replace the second statistics by statistics on expressions
 433
 434 DROP STATISTICS s12;
 435
 436 CREATE STATISTICS s12 (ndistinct) ON (c * 10), (d - 1) FROM ndistinct;
 437
 438 ANALYZE ndistinct;
 439
 440 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 441
 442 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 443
 444 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 445
 446 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 447
 448 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 449
 450 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 451
 452 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 453
 454
 455 -- replace the second statistics by statistics on both attributes and expressions
 456
 457 DROP STATISTICS s12;
 458
 459 CREATE STATISTICS s12 (ndistinct) ON c, d, (c * 10), (d - 1) FROM ndistinct;
 460
 461 ANALYZE ndistinct;
 462
 463 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 464
 465 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 466
 467 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 468
 469 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 470
 471 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 472
 473 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 474
 475 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 476
 477
 478 -- replace the other statistics by statistics on both attributes and expressions
 479
 480 DROP STATISTICS s11;
 481
 482 CREATE STATISTICS s11 (ndistinct) ON a, b, (a*5), (b+1) FROM ndistinct;
 483
 484 ANALYZE ndistinct;
 485
 486 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 487
 488 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 489
 490 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 491
 492 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 493
 494 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 495
 496 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 497
 498 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 499
 500
 501 -- replace statistics by somewhat overlapping ones (this expected to get worse estimate
 502 -- because the first statistics shall be applied to 3 columns, and the second one can't
 503 -- be really applied)
 504
 505 DROP STATISTICS s11;
 506 DROP STATISTICS s12;
 507
 508 CREATE STATISTICS s11 (ndistinct) ON a, b, (a*5), (b+1) FROM ndistinct;
 509 CREATE STATISTICS s12 (ndistinct) ON a, (b+1), (c * 10) FROM ndistinct;
 510
 511 ANALYZE ndistinct;
 512
 513 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
 514
 515 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1)');
 516
 517 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), b');
 518
 519 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1)');
 520
 521 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a*5), (b+1), c');
 522
 523 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, (c*10)');
 524
 525 SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (b+1), c, (d - 1)');
 526
 527 DROP STATISTICS s11;
 528 DROP STATISTICS s12;
 529
 530 -- functional dependencies tests
 531 CREATE TABLE functional_dependencies (
 532     filler1 TEXT,
 533     filler2 NUMERIC,
 534     a INT,
 535     b TEXT,
 536     filler3 DATE,
 537     c INT,
 538     d TEXT
 539 )
 540 WITH (autovacuum_enabled = off);
 541
 542 CREATE INDEX fdeps_ab_idx ON functional_dependencies (a, b);
 543 CREATE INDEX fdeps_abc_idx ON functional_dependencies (a, b, c);
 544
 545 -- random data (no functional dependencies)
 546 INSERT INTO functional_dependencies (a, b, c, filler1)
 547      SELECT mod(i, 5), mod(i, 7), mod(i, 11), i FROM generate_series(1,1000) s(i);
 548
 549 ANALYZE functional_dependencies;
 550
 551 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
 552
 553 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 554
 555 -- create statistics
 556 CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
 557
 558 ANALYZE functional_dependencies;
 559
 560 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
 561
 562 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 563
 564 -- a => b, a => c, b => c
 565 TRUNCATE functional_dependencies;
 566 DROP STATISTICS func_deps_stat;
 567
 568 -- now do the same thing, but with expressions
 569 INSERT INTO functional_dependencies (a, b, c, filler1)
 570      SELECT i, i, i, i FROM generate_series(1,5000) s(i);
 571
 572 ANALYZE functional_dependencies;
 573
 574 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE mod(a, 11) = 1 AND mod(b::int, 13) = 1');
 575
 576 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE mod(a, 11) = 1 AND mod(b::int, 13) = 1 AND mod(c, 7) = 1');
 577
 578 -- create statistics
 579 CREATE STATISTICS func_deps_stat (dependencies) ON (mod(a,11)), (mod(b::int, 13)), (mod(c, 7)) FROM functional_dependencies;
 580
 581 ANALYZE functional_dependencies;
 582
 583 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE mod(a, 11) = 1 AND mod(b::int, 13) = 1');
 584
 585 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE mod(a, 11) = 1 AND mod(b::int, 13) = 1 AND mod(c, 7) = 1');
 586
 587 -- a => b, a => c, b => c
 588 TRUNCATE functional_dependencies;
 589 DROP STATISTICS func_deps_stat;
 590
 591 INSERT INTO functional_dependencies (a, b, c, filler1)
 592      SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
 593
 594 ANALYZE functional_dependencies;
 595
 596 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
 597
 598 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 599
 600 -- IN
 601 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ''1''');
 602
 603 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b IN (''1'', ''2'')');
 604
 605 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b IN (''1'', ''2'')');
 606
 607 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b = ''1''');
 608
 609 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 26, 51, 76) AND b IN (''1'', ''26'') AND c = 1');
 610
 611 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 26, 51, 76) AND b IN (''1'', ''26'') AND c IN (1)');
 612
 613 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 26, 27, 51, 52, 76, 77) AND b IN (''1'', ''2'', ''26'', ''27'') AND c IN (1, 2)');
 614
 615 -- OR clauses referencing the same attribute
 616 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
 617
 618 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
 619
 620 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
 621
 622 -- OR clauses referencing different attributes
 623 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
 624
 625 -- ANY
 626 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 51]) AND b = ''1''');
 627
 628 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 51]) AND b = ANY (ARRAY[''1'', ''2''])');
 629
 630 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
 631
 632 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 26, 51, 76]) AND b = ANY (ARRAY[''1'', ''26'']) AND c = 1');
 633
 634 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 26, 51, 76]) AND b = ANY (ARRAY[''1'', ''26'']) AND c = ANY (ARRAY[1])');
 635
 636 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 2, 26, 27, 51, 52, 76, 77]) AND b = ANY (ARRAY[''1'', ''2'', ''26'', ''27'']) AND c = ANY (ARRAY[1, 2])');
 637
 638 -- ANY with inequalities should not benefit from functional dependencies
 639 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a < ANY (ARRAY[1, 51]) AND b > ''1''');
 640
 641 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a >= ANY (ARRAY[1, 51]) AND b <= ANY (ARRAY[''1'', ''2''])');
 642
 643 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a <= ANY (ARRAY[1, 2, 51, 52]) AND b >= ANY (ARRAY[''1'', ''2''])');
 644
 645 -- ALL (should not benefit from functional dependencies)
 646 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ALL (ARRAY[''1''])');
 647
 648 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ALL (ARRAY[''1'', ''2''])');
 649
 650 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b = ALL (ARRAY[''1'', ''2''])');
 651
 652 -- create statistics
 653 CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
 654
 655 ANALYZE functional_dependencies;
 656
 657 -- print the detected dependencies
 658 SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
 659
 660 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
 661
 662 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 663
 664 -- IN
 665 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ''1''');
 666
 667 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b IN (''1'', ''2'')');
 668
 669 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b IN (''1'', ''2'')');
 670
 671 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b = ''1''');
 672
 673 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 26, 51, 76) AND b IN (''1'', ''26'') AND c = 1');
 674
 675 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 26, 51, 76) AND b IN (''1'', ''26'') AND c IN (1)');
 676
 677 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 26, 27, 51, 52, 76, 77) AND b IN (''1'', ''2'', ''26'', ''27'') AND c IN (1, 2)');
 678
 679 -- OR clauses referencing the same attribute
 680 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
 681
 682 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
 683
 684 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
 685
 686 -- OR clauses referencing different attributes are incompatible
 687 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
 688
 689 -- ANY
 690 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 51]) AND b = ''1''');
 691
 692 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 51]) AND b = ANY (ARRAY[''1'', ''2''])');
 693
 694 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
 695
 696 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 26, 51, 76]) AND b = ANY (ARRAY[''1'', ''26'']) AND c = 1');
 697
 698 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 26, 51, 76]) AND b = ANY (ARRAY[''1'', ''26'']) AND c = ANY (ARRAY[1])');
 699
 700 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = ANY (ARRAY[1, 2, 26, 27, 51, 52, 76, 77]) AND b = ANY (ARRAY[''1'', ''2'', ''26'', ''27'']) AND c = ANY (ARRAY[1, 2])');
 701
 702 -- ANY with inequalities should not benefit from functional dependencies
 703 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a < ANY (ARRAY[1, 51]) AND b > ''1''');
 704
 705 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a >= ANY (ARRAY[1, 51]) AND b <= ANY (ARRAY[''1'', ''2''])');
 706
 707 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a <= ANY (ARRAY[1, 2, 51, 52]) AND b >= ANY (ARRAY[''1'', ''2''])');
 708
 709 -- ALL (should not benefit from functional dependencies)
 710 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ALL (ARRAY[''1''])');
 711
 712 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 51) AND b = ALL (ARRAY[''1'', ''2''])');
 713
 714 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a IN (1, 2, 51, 52) AND b = ALL (ARRAY[''1'', ''2''])');
 715
 716 -- changing the type of column c causes all its stats to be dropped, reverting
 717 -- to default estimates without any statistics, i.e. 0.5% selectivity for each
 718 -- condition
 719 ALTER TABLE functional_dependencies ALTER COLUMN c TYPE numeric;
 720
 721 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 722
 723 ANALYZE functional_dependencies;
 724
 725 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');
 726
 727 DROP STATISTICS func_deps_stat;
 728
 729 -- now try functional dependencies with expressions
 730
 731 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
 732
 733 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1'' AND (c + 1) = 2');
 734
 735 -- IN
 736 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ''1''');
 737
 738 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) IN (''1'', ''2'')');
 739
 740 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) IN (''1'', ''2'')');
 741
 742 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) = ''1''');
 743
 744 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 52, 102, 152) AND upper(b) IN (''1'', ''26'') AND (c + 1) = 2');
 745
 746 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 52, 102, 152) AND upper(b) IN (''1'', ''26'') AND (c + 1) IN (2)');
 747
 748 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 52, 54, 102, 104, 152, 154) AND upper(b) IN (''1'', ''2'', ''26'', ''27'') AND (c + 1) IN (2, 3)');
 749
 750 -- OR clauses referencing the same attribute
 751 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
 752
 753 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
 754
 755 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
 756
 757 -- OR clauses referencing different attributes
 758 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
 759
 760 -- ANY
 761 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 102]) AND upper(b) = ''1''');
 762
 763 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 102]) AND upper(b) = ANY (ARRAY[''1'', ''2''])');
 764
 765 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 4, 102, 104]) AND upper(b) = ANY (ARRAY[''1'', ''2''])');
 766
 767 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 52, 102, 152]) AND upper(b) = ANY (ARRAY[''1'', ''26'']) AND (c + 1) = 2');
 768
 769 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 52, 102, 152]) AND upper(b) = ANY (ARRAY[''1'', ''26'']) AND (c + 1) = ANY (ARRAY[2])');
 770
 771 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 4, 52, 54, 102, 104, 152, 154]) AND upper(b) = ANY (ARRAY[''1'', ''2'', ''26'', ''27'']) AND (c + 1) = ANY (ARRAY[2, 3])');
 772
 773 -- ANY with inequalities should not benefit from functional dependencies
 774 -- the estimates however improve thanks to having expression statistics
 775 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) < ANY (ARRAY[2, 102]) AND upper(b) > ''1''');
 776
 777 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) >= ANY (ARRAY[2, 102]) AND upper(b) <= ANY (ARRAY[''1'', ''2''])');
 778
 779 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) <= ANY (ARRAY[2, 4, 102, 104]) AND upper(b) >= ANY (ARRAY[''1'', ''2''])');
 780
 781 -- ALL (should not benefit from functional dependencies)
 782 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ALL (ARRAY[''1''])');
 783
 784 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ALL (ARRAY[''1'', ''2''])');
 785
 786 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) = ALL (ARRAY[''1'', ''2''])');
 787
 788 -- create statistics on expressions
 789 CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FROM functional_dependencies;
 790
 791 ANALYZE functional_dependencies;
 792
 793 -- print the detected dependencies
 794 SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
 795
 796 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
 797
 798 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1'' AND (c + 1) = 2');
 799
 800 -- IN
 801 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ''1''');
 802
 803 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) IN (''1'', ''2'')');
 804
 805 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) IN (''1'', ''2'')');
 806
 807 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) = ''1''');
 808
 809 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 52, 102, 152) AND upper(b) IN (''1'', ''26'') AND (c + 1) = 2');
 810
 811 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 52, 102, 152) AND upper(b) IN (''1'', ''26'') AND (c + 1) IN (2)');
 812
 813 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 52, 54, 102, 104, 152, 154) AND upper(b) IN (''1'', ''2'', ''26'', ''27'') AND (c + 1) IN (2, 3)');
 814
 815 -- OR clauses referencing the same attribute
 816 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
 817
 818 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
 819
 820 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
 821
 822 -- OR clauses referencing different attributes
 823 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
 824
 825 -- ANY
 826 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 102]) AND upper(b) = ''1''');
 827
 828 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 102]) AND upper(b) = ANY (ARRAY[''1'', ''2''])');
 829
 830 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 4, 102, 104]) AND upper(b) = ANY (ARRAY[''1'', ''2''])');
 831
 832 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 52, 102, 152]) AND upper(b) = ANY (ARRAY[''1'', ''26'']) AND (c + 1) = 2');
 833
 834 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 52, 102, 152]) AND upper(b) = ANY (ARRAY[''1'', ''26'']) AND (c + 1) = ANY (ARRAY[2])');
 835
 836 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = ANY (ARRAY[2, 4, 52, 54, 102, 104, 152, 154]) AND upper(b) = ANY (ARRAY[''1'', ''2'', ''26'', ''27'']) AND (c + 1) = ANY (ARRAY[2, 3])');
 837
 838 -- ANY with inequalities should not benefit from functional dependencies
 839 -- the estimates however improve thanks to having expression statistics
 840 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) < ANY (ARRAY[2, 102]) AND upper(b) > ''1''');
 841
 842 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) >= ANY (ARRAY[2, 102]) AND upper(b) <= ANY (ARRAY[''1'', ''2''])');
 843
 844 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) <= ANY (ARRAY[2, 4, 102, 104]) AND upper(b) >= ANY (ARRAY[''1'', ''2''])');
 845
 846 -- ALL (should not benefit from functional dependencies)
 847 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ALL (ARRAY[''1''])');
 848
 849 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 102) AND upper(b) = ALL (ARRAY[''1'', ''2''])');
 850
 851 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) IN (2, 4, 102, 104) AND upper(b) = ALL (ARRAY[''1'', ''2''])');
 852
 853 -- check the ability to use multiple functional dependencies
 854 CREATE TABLE functional_dependencies_multi (
 855         a INTEGER,
 856         b INTEGER,
 857         c INTEGER,
 858         d INTEGER
 859 )
 860 WITH (autovacuum_enabled = off);
 861
 862 INSERT INTO functional_dependencies_multi (a, b, c, d)
 863     SELECT
 864          mod(i,7),
 865          mod(i,7),
 866          mod(i,11),
 867          mod(i,11)
 868     FROM generate_series(1,5000) s(i);
 869
 870 ANALYZE functional_dependencies_multi;
 871
 872 -- estimates without any functional dependencies
 873 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
 874 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
 875 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
 876 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
 877 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
 878
 879 -- create separate functional dependencies
 880 CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM functional_dependencies_multi;
 881 CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM functional_dependencies_multi;
 882
 883 ANALYZE functional_dependencies_multi;
 884
 885 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
 886 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
 887 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE c = 0 AND d = 0');
 888 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
 889 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND b = 0 AND 0 = c AND d = 0');
 890
 891 DROP TABLE functional_dependencies_multi;
 892
 893 -- MCV lists
 894 CREATE TABLE mcv_lists (
 895     filler1 TEXT,
 896     filler2 NUMERIC,
 897     a INT,
 898     b VARCHAR,
 899     filler3 DATE,
 900     c INT,
 901     d TEXT
 902 )
 903 WITH (autovacuum_enabled = off);
 904
 905 -- random data (no MCV list)
 906 INSERT INTO mcv_lists (a, b, c, filler1)
 907      SELECT mod(i,37), mod(i,41), mod(i,43), mod(i,47) FROM generate_series(1,5000) s(i);
 908
 909 ANALYZE mcv_lists;
 910
 911 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 912
 913 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
 914
 915 -- create statistics
 916 CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
 917
 918 ANALYZE mcv_lists;
 919
 920 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 921
 922 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
 923
 924 TRUNCATE mcv_lists;
 925 DROP STATISTICS mcv_lists_stats;
 926
 927 -- random data (no MCV list), but with expression
 928 INSERT INTO mcv_lists (a, b, c, filler1)
 929      SELECT i, i, i, i FROM generate_series(1,1000) s(i);
 930
 931 ANALYZE mcv_lists;
 932
 933 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,7) = 1 AND mod(b::int,11) = 1');
 934
 935 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,7) = 1 AND mod(b::int,11) = 1 AND mod(c,13) = 1');
 936
 937 -- create statistics
 938 CREATE STATISTICS mcv_lists_stats (mcv) ON (mod(a,7)), (mod(b::int,11)), (mod(c,13)) FROM mcv_lists;
 939
 940 ANALYZE mcv_lists;
 941
 942 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,7) = 1 AND mod(b::int,11) = 1');
 943
 944 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,7) = 1 AND mod(b::int,11) = 1 AND mod(c,13) = 1');
 945
 946 -- 100 distinct combinations, all in the MCV list
 947 TRUNCATE mcv_lists;
 948 DROP STATISTICS mcv_lists_stats;
 949
 950 INSERT INTO mcv_lists (a, b, c, filler1)
 951      SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
 952
 953 ANALYZE mcv_lists;
 954
 955 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
 956
 957 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = a AND ''1'' = b');
 958
 959 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 1 AND b < ''1''');
 960
 961 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > a AND ''1'' > b');
 962
 963 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 0 AND b <= ''0''');
 964
 965 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 0 >= a AND ''0'' >= b');
 966
 967 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
 968
 969 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND b < ''1'' AND c < 5');
 970
 971 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND ''1'' > b AND 5 > c');
 972
 973 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 4 AND b <= ''0'' AND c <= 4');
 974
 975 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 4 >= a AND ''0'' >= b AND 4 >= c');
 976
 977 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1');
 978
 979 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL');
 980
 981 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')');
 982
 983 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)');
 984
 985 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
 986
 987 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])');
 988
 989 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')');
 990
 991 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')');
 992
 993 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])');
 994
 995 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])');
 996
 997 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])');
 998
 999 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])');
1000
1001 -- create statistics
1002 CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
1003
1004 ANALYZE mcv_lists;
1005
1006 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
1007
1008 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = a AND ''1'' = b');
1009
1010 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 1 AND b < ''1''');
1011
1012 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > a AND ''1'' > b');
1013
1014 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 0 AND b <= ''0''');
1015
1016 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 0 >= a AND ''0'' >= b');
1017
1018 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1'' AND c = 1');
1019
1020 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND b < ''1'' AND c < 5');
1021
1022 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < 5 AND ''1'' > b AND 5 > c');
1023
1024 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= 4 AND b <= ''0'' AND c <= 4');
1025
1026 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 4 >= a AND ''0'' >= b AND 4 >= c');
1027
1028 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1');
1029
1030 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL');
1031
1032 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''1'' OR c = 1 OR d IS NOT NULL');
1033
1034 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52) AND b IN ( ''1'', ''2'')');
1035
1036 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (1, 2, 51, 52, NULL) AND b IN ( ''1'', ''2'', NULL)');
1037
1038 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = ANY (ARRAY[1, 2, 51, 52]) AND b = ANY (ARRAY[''1'', ''2''])');
1039
1040 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])');
1041
1042 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, 2, 3]) AND b IN (''1'', ''2'', ''3'')');
1043
1044 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a <= ANY (ARRAY[1, NULL, 2, 3]) AND b IN (''1'', ''2'', NULL, ''3'')');
1045
1046 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3])');
1047
1048 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY[4, 5]) AND c > ANY (ARRAY[1, 2, 3, NULL])');
1049
1050 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])');
1051
1052 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])');
1053
1054 -- check change of unrelated column type does not reset the MCV statistics
1055 ALTER TABLE mcv_lists ALTER COLUMN d TYPE VARCHAR(64);
1056
1057 SELECT d.stxdmcv IS NOT NULL
1058   FROM pg_statistic_ext s, pg_statistic_ext_data d
1059  WHERE s.stxname = 'mcv_lists_stats'
1060    AND d.stxoid = s.oid;
1061
1062 -- check change of column type resets the MCV statistics
1063 ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
1064
1065 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
1066
1067 ANALYZE mcv_lists;
1068
1069 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
1070
1071
1072 -- 100 distinct combinations, all in the MCV list, but with expressions
1073 TRUNCATE mcv_lists;
1074 DROP STATISTICS mcv_lists_stats;
1075
1076 INSERT INTO mcv_lists (a, b, c, filler1)
1077      SELECT i, i, i, i FROM generate_series(1,1000) s(i);
1078
1079 ANALYZE mcv_lists;
1080
1081 -- without any stats on the expressions, we have to use default selectivities, which
1082 -- is why the estimates here are different from the pre-computed case above
1083
1084 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1');
1085
1086 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = mod(a,20) AND 1 = mod(b::int,10)');
1087
1088 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < 1 AND mod(b::int,10) < 1');
1089
1090 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > mod(a,20) AND 1 > mod(b::int,10)');
1091
1092 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1 AND mod(c,5) = 1');
1093
1094 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 OR mod(b::int,10) = 1 OR mod(c,25) = 1 OR d IS NOT NULL');
1095
1096 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) IN (1, 2, 51, 52, NULL) AND mod(b::int,10) IN ( 1, 2, NULL)');
1097
1098 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = ANY (ARRAY[1, 2, 51, 52]) AND mod(b::int,10) = ANY (ARRAY[1, 2])');
1099
1100 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) <= ANY (ARRAY[1, NULL, 2, 3]) AND mod(b::int,10) IN (1, 2, NULL, 3)');
1101
1102 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < ALL (ARRAY[4, 5]) AND mod(b::int,10) IN (1, 2, 3) AND mod(c,5) > ANY (ARRAY[1, 2, 3])');
1103
1104 -- create statistics with expressions only (we create three separate stats, in order not to build more complex extended stats)
1105 CREATE STATISTICS mcv_lists_stats_1 ON (mod(a,20)) FROM mcv_lists;
1106 CREATE STATISTICS mcv_lists_stats_2 ON (mod(b::int,10)) FROM mcv_lists;
1107 CREATE STATISTICS mcv_lists_stats_3 ON (mod(c,5)) FROM mcv_lists;
1108
1109 ANALYZE mcv_lists;
1110
1111 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1');
1112
1113 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = mod(a,20) AND 1 = mod(b::int,10)');
1114
1115 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < 1 AND mod(b::int,10) < 1');
1116
1117 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > mod(a,20) AND 1 > mod(b::int,10)');
1118
1119 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1 AND mod(c,5) = 1');
1120
1121 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 OR mod(b::int,10) = 1 OR mod(c,25) = 1 OR d IS NOT NULL');
1122
1123 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) IN (1, 2, 51, 52, NULL) AND mod(b::int,10) IN ( 1, 2, NULL)');
1124
1125 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = ANY (ARRAY[1, 2, 51, 52]) AND mod(b::int,10) = ANY (ARRAY[1, 2])');
1126
1127 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) <= ANY (ARRAY[1, NULL, 2, 3]) AND mod(b::int,10) IN (1, 2, NULL, 3)');
1128
1129 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < ALL (ARRAY[4, 5]) AND mod(b::int,10) IN (1, 2, 3) AND mod(c,5) > ANY (ARRAY[1, 2, 3])');
1130
1131 DROP STATISTICS mcv_lists_stats_1;
1132 DROP STATISTICS mcv_lists_stats_2;
1133 DROP STATISTICS mcv_lists_stats_3;
1134
1135 -- create statistics with both MCV and expressions
1136 CREATE STATISTICS mcv_lists_stats (mcv) ON (mod(a,20)), (mod(b::int,10)), (mod(c,5)) FROM mcv_lists;
1137
1138 ANALYZE mcv_lists;
1139
1140 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1');
1141
1142 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 = mod(a,20) AND 1 = mod(b::int,10)');
1143
1144 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < 1 AND mod(b::int,10) < 1');
1145
1146 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE 1 > mod(a,20) AND 1 > mod(b::int,10)');
1147
1148 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 AND mod(b::int,10) = 1 AND mod(c,5) = 1');
1149
1150 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 OR mod(b::int,10) = 1 OR mod(c,25) = 1 OR d IS NOT NULL');
1151
1152 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) IN (1, 2, 51, 52, NULL) AND mod(b::int,10) IN ( 1, 2, NULL)');
1153
1154 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = ANY (ARRAY[1, 2, 51, 52]) AND mod(b::int,10) = ANY (ARRAY[1, 2])');
1155
1156 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) <= ANY (ARRAY[1, NULL, 2, 3]) AND mod(b::int,10) IN (1, 2, NULL, 3)');
1157
1158 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) < ALL (ARRAY[4, 5]) AND mod(b::int,10) IN (1, 2, 3) AND mod(c,5) > ANY (ARRAY[1, 2, 3])');
1159
1160 -- we can't use the statistic for OR clauses that are not fully covered (missing 'd' attribute)
1161 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE mod(a,20) = 1 OR mod(b::int,10) = 1 OR mod(c,5) = 1 OR d IS NOT NULL');
1162
1163 -- 100 distinct combinations with NULL values, all in the MCV list
1164 TRUNCATE mcv_lists;
1165 DROP STATISTICS mcv_lists_stats;
1166
1167 INSERT INTO mcv_lists (a, b, c, filler1)
1168      SELECT
1169          (CASE WHEN mod(i,100) = 1 THEN NULL ELSE mod(i,100) END),
1170          (CASE WHEN mod(i,50) = 1  THEN NULL ELSE mod(i,50) END),
1171          (CASE WHEN mod(i,25) = 1  THEN NULL ELSE mod(i,25) END),
1172          i
1173      FROM generate_series(1,5000) s(i);
1174
1175 ANALYZE mcv_lists;
1176
1177 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
1178
1179 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL AND c IS NULL');
1180
1181 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NOT NULL');
1182
1183 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NOT NULL AND b IS NULL AND c IS NOT NULL');
1184
1185 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AND b IN (''0'', ''1'')');
1186
1187 -- create statistics
1188 CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
1189
1190 ANALYZE mcv_lists;
1191
1192 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
1193
1194 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL AND c IS NULL');
1195
1196 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NOT NULL');
1197
1198 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NOT NULL AND b IS NULL AND c IS NOT NULL');
1199
1200 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AND b IN (''0'', ''1'')');
1201
1202 -- test pg_mcv_list_items with a very simple (single item) MCV list
1203 TRUNCATE mcv_lists;
1204 INSERT INTO mcv_lists (a, b, c) SELECT 1, 2, 3 FROM generate_series(1,1000) s(i);
1205 ANALYZE mcv_lists;
1206
1207 SELECT m.*
1208   FROM pg_statistic_ext s, pg_statistic_ext_data d,
1209        pg_mcv_list_items(d.stxdmcv) m
1210  WHERE s.stxname = 'mcv_lists_stats'
1211    AND d.stxoid = s.oid;
1212
1213 -- 2 distinct combinations with NULL values, all in the MCV list
1214 TRUNCATE mcv_lists;
1215 DROP STATISTICS mcv_lists_stats;
1216
1217 INSERT INTO mcv_lists (a, b, c, d)
1218      SELECT
1219          NULL, -- always NULL
1220          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END),
1221          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 0 END),
1222          (CASE WHEN mod(i,2) = 0 THEN NULL ELSE 'x' END)
1223      FROM generate_series(1,5000) s(i);
1224
1225 ANALYZE mcv_lists;
1226
1227 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
1228
1229 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''x'' OR d = ''x''');
1230
1231 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')');
1232
1233 -- create statistics
1234 CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, d FROM mcv_lists;
1235
1236 ANALYZE mcv_lists;
1237
1238 -- test pg_mcv_list_items with MCV list containing variable-length data and NULLs
1239 SELECT m.*
1240   FROM pg_statistic_ext s, pg_statistic_ext_data d,
1241        pg_mcv_list_items(d.stxdmcv) m
1242  WHERE s.stxname = 'mcv_lists_stats'
1243    AND d.stxoid = s.oid;
1244
1245 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
1246
1247 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 OR b = ''x'' OR d = ''x''');
1248
1249 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND (b = ''x'' OR d = ''x'')');
1250
1251 -- mcv with pass-by-ref fixlen types, e.g. uuid
1252 CREATE TABLE mcv_lists_uuid (
1253     a UUID,
1254     b UUID,
1255     c UUID
1256 )
1257 WITH (autovacuum_enabled = off);
1258
1259 INSERT INTO mcv_lists_uuid (a, b, c)
1260      SELECT
1261          md5(mod(i,100)::text)::uuid,
1262          md5(mod(i,50)::text)::uuid,
1263          md5(mod(i,25)::text)::uuid
1264      FROM generate_series(1,5000) s(i);
1265
1266 ANALYZE mcv_lists_uuid;
1267
1268 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1269
1270 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''');
1271
1272 CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
1273   FROM mcv_lists_uuid;
1274
1275 ANALYZE mcv_lists_uuid;
1276
1277 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
1278
1279 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''');
1280
1281 DROP TABLE mcv_lists_uuid;
1282
1283 -- mcv with arrays
1284 CREATE TABLE mcv_lists_arrays (
1285     a TEXT[],
1286     b NUMERIC[],
1287     c INT[]
1288 )
1289 WITH (autovacuum_enabled = off);
1290
1291 INSERT INTO mcv_lists_arrays (a, b, c)
1292      SELECT
1293          ARRAY[md5((i/100)::text), md5((i/100-1)::text), md5((i/100+1)::text)],
1294          ARRAY[(i/100-1)::numeric/1000, (i/100)::numeric/1000, (i/100+1)::numeric/1000],
1295          ARRAY[(i/100-1), i/100, (i/100+1)]
1296      FROM generate_series(1,5000) s(i);
1297
1298 CREATE STATISTICS mcv_lists_arrays_stats (mcv) ON a, b, c
1299   FROM mcv_lists_arrays;
1300
1301 ANALYZE mcv_lists_arrays;
1302
1303 -- mcv with bool
1304 CREATE TABLE mcv_lists_bool (
1305     a BOOL,
1306     b BOOL,
1307     c BOOL
1308 )
1309 WITH (autovacuum_enabled = off);
1310
1311 INSERT INTO mcv_lists_bool (a, b, c)
1312      SELECT
1313          (mod(i,2) = 0), (mod(i,4) = 0), (mod(i,8) = 0)
1314      FROM generate_series(1,10000) s(i);
1315
1316 ANALYZE mcv_lists_bool;
1317
1318 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE a AND b AND c');
1319
1320 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND b AND c');
1321
1322 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND NOT b AND c');
1323
1324 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND b AND NOT c');
1325
1326 CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c
1327   FROM mcv_lists_bool;
1328
1329 ANALYZE mcv_lists_bool;
1330
1331 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE a AND b AND c');
1332
1333 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND b AND c');
1334
1335 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND NOT b AND c');
1336
1337 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND b AND NOT c');
1338
1339 -- mcv covering just a small fraction of data
1340 CREATE TABLE mcv_lists_partial (
1341     a INT,
1342     b INT,
1343     c INT
1344 );
1345
1346 -- 10 frequent groups, each with 100 elements
1347 INSERT INTO mcv_lists_partial (a, b, c)
1348      SELECT
1349          mod(i,10),
1350          mod(i,10),
1351          mod(i,10)
1352      FROM generate_series(0,999) s(i);
1353
1354 -- 100 groups that will make it to the MCV list (includes the 10 frequent ones)
1355 INSERT INTO mcv_lists_partial (a, b, c)
1356      SELECT
1357          i,
1358          i,
1359          i
1360      FROM generate_series(0,99) s(i);
1361
1362 -- 4000 groups in total, most of which won't make it (just a single item)
1363 INSERT INTO mcv_lists_partial (a, b, c)
1364      SELECT
1365          i,
1366          i,
1367          i
1368      FROM generate_series(0,3999) s(i);
1369
1370 ANALYZE mcv_lists_partial;
1371
1372 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 AND b = 0 AND c = 0');
1373
1374 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 OR b = 0 OR c = 0');
1375
1376 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 10 AND b = 10 AND c = 10');
1377
1378 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 10 OR b = 10 OR c = 10');
1379
1380 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 AND b = 0 AND c = 10');
1381
1382 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 OR b = 0 OR c = 10');
1383
1384 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 AND b = 0 AND c = 0) OR (a = 1 AND b = 1 AND c = 1) OR (a = 2 AND b = 2 AND c = 2)');
1385
1386 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 AND b = 0) OR (a = 0 AND c = 0) OR (b = 0 AND c = 0)');
1387
1388 CREATE STATISTICS mcv_lists_partial_stats (mcv) ON a, b, c
1389   FROM mcv_lists_partial;
1390
1391 ANALYZE mcv_lists_partial;
1392
1393 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 AND b = 0 AND c = 0');
1394
1395 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 OR b = 0 OR c = 0');
1396
1397 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 10 AND b = 10 AND c = 10');
1398
1399 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 10 OR b = 10 OR c = 10');
1400
1401 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 AND b = 0 AND c = 10');
1402
1403 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE a = 0 OR b = 0 OR c = 10');
1404
1405 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 AND b = 0 AND c = 0) OR (a = 1 AND b = 1 AND c = 1) OR (a = 2 AND b = 2 AND c = 2)');
1406
1407 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_partial WHERE (a = 0 AND b = 0) OR (a = 0 AND c = 0) OR (b = 0 AND c = 0)');
1408
1409 DROP TABLE mcv_lists_partial;
1410
1411 -- check the ability to use multiple MCV lists
1412 CREATE TABLE mcv_lists_multi (
1413         a INTEGER,
1414         b INTEGER,
1415         c INTEGER,
1416         d INTEGER
1417 )
1418 WITH (autovacuum_enabled = off);
1419
1420 INSERT INTO mcv_lists_multi (a, b, c, d)
1421     SELECT
1422          mod(i,5),
1423          mod(i,5),
1424          mod(i,7),
1425          mod(i,7)
1426     FROM generate_series(1,5000) s(i);
1427
1428 ANALYZE mcv_lists_multi;
1429
1430 -- estimates without any mcv statistics
1431 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0');
1432 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0 AND d = 0');
1433 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 AND c = 0');
1434 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 OR c = 0');
1435 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
1436 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE (a = 0 AND b = 0) OR (c = 0 AND d = 0)');
1437 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR b = 0 OR c = 0 OR d = 0');
1438
1439 -- create separate MCV statistics
1440 CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM mcv_lists_multi;
1441 CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi;
1442
1443 ANALYZE mcv_lists_multi;
1444
1445 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0');
1446 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0 AND d = 0');
1447 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 AND c = 0');
1448 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE b = 0 OR c = 0');
1449 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0 AND c = 0 AND d = 0');
1450 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE (a = 0 AND b = 0) OR (c = 0 AND d = 0)');
1451 SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 OR b = 0 OR c = 0 OR d = 0');
1452
1453 DROP TABLE mcv_lists_multi;
1454
1455
1456 -- statistics on integer expressions
1457 CREATE TABLE expr_stats (a int, b int, c int);
1458 INSERT INTO expr_stats SELECT mod(i,10), mod(i,10), mod(i,10) FROM generate_series(1,1000) s(i);
1459 ANALYZE expr_stats;
1460
1461 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE (2*a) = 0 AND (3*b) = 0');
1462 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE (a+b) = 0 AND (a-b) = 0');
1463
1464 CREATE STATISTICS expr_stats_1 (mcv) ON (a+b), (a-b), (2*a), (3*b) FROM expr_stats;
1465 ANALYZE expr_stats;
1466
1467 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE (2*a) = 0 AND (3*b) = 0');
1468 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE (a+b) = 0 AND (a-b) = 0');
1469
1470 DROP STATISTICS expr_stats_1;
1471 DROP TABLE expr_stats;
1472
1473 -- statistics on a mix columns and expressions
1474 CREATE TABLE expr_stats (a int, b int, c int);
1475 INSERT INTO expr_stats SELECT mod(i,10), mod(i,10), mod(i,10) FROM generate_series(1,1000) s(i);
1476 ANALYZE expr_stats;
1477
1478 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (2*a) = 0 AND (3*b) = 0');
1479 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 3 AND b = 3 AND (a-b) = 0');
1480 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND b = 1 AND (a-b) = 0');
1481
1482 CREATE STATISTICS expr_stats_1 (mcv) ON a, b, (2*a), (3*b), (a+b), (a-b) FROM expr_stats;
1483 ANALYZE expr_stats;
1484
1485 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (2*a) = 0 AND (3*b) = 0');
1486 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 3 AND b = 3 AND (a-b) = 0');
1487 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND b = 1 AND (a-b) = 0');
1488
1489 DROP TABLE expr_stats;
1490
1491 -- statistics on expressions with different data types
1492 CREATE TABLE expr_stats (a int, b name, c text);
1493 INSERT INTO expr_stats SELECT mod(i,10), md5(mod(i,10)::text), md5(mod(i,10)::text) FROM generate_series(1,1000) s(i);
1494 ANALYZE expr_stats;
1495
1496 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (b || c) <= ''z'' AND (c || b) >= ''0''');
1497
1498 CREATE STATISTICS expr_stats_1 (mcv) ON a, b, (b || c), (c || b) FROM expr_stats;
1499 ANALYZE expr_stats;
1500
1501 SELECT * FROM check_estimated_rows('SELECT * FROM expr_stats WHERE a = 0 AND (b || c) <= ''z'' AND (c || b) >= ''0''');
1502
1503 DROP TABLE expr_stats;
1504
1505 -- test handling of a mix of compatible and incompatible expressions
1506 CREATE TABLE expr_stats_incompatible_test (
1507     c0 double precision,
1508     c1 boolean NOT NULL
1509 );
1510
1511 CREATE STATISTICS expr_stat_comp_1 ON c0, c1 FROM expr_stats_incompatible_test;
1512
1513 INSERT INTO expr_stats_incompatible_test VALUES (1234,false), (5678,true);
1514 ANALYZE expr_stats_incompatible_test;
1515
1516 SELECT c0 FROM ONLY expr_stats_incompatible_test WHERE
1517 (
1518   upper('x') LIKE ('x'||('[0,1]'::int4range))
1519   AND
1520   (c0 IN (0, 1) OR c1)
1521 );
1522
1523 DROP TABLE expr_stats_incompatible_test;
1524
1525 -- Permission tests. Users should not be able to see specific data values in
1526 -- the extended statistics, if they lack permission to see those values in
1527 -- the underlying table.
1528 --
1529 -- Currently this is only relevant for MCV stats.
1530 CREATE SCHEMA tststats;
1531
1532 CREATE TABLE tststats.priv_test_tbl (
1533     a int,
1534     b int
1535 );
1536
1537 INSERT INTO tststats.priv_test_tbl
1538      SELECT mod(i,5), mod(i,10) FROM generate_series(1,100) s(i);
1539
1540 CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
1541   FROM tststats.priv_test_tbl;
1542
1543 ANALYZE tststats.priv_test_tbl;
1544
1545 -- Check printing info about extended statistics by \dX
1546 create table stts_t1 (a int, b int);
1547 create statistics stts_1 (ndistinct) on a, b from stts_t1;
1548 create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
1549 create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
1550
1551 create table stts_t2 (a int, b int, c int);
1552 create statistics stts_4 on b, c from stts_t2;
1553
1554 create table stts_t3 (col1 int, col2 int, col3 int);
1555 create statistics stts_hoge on col1, col2, col3 from stts_t3;
1556
1557 create schema stts_s1;
1558 create schema stts_s2;
1559 create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
1560 create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
1561
1562 insert into stts_t1 select i,i from generate_series(1,100) i;
1563 analyze stts_t1;
1564 set search_path to public, stts_s1, stts_s2, tststats;
1565
1566 \dX
1567 \dX stts_?
1568 \dX *stts_hoge
1569 \dX+
1570 \dX+ stts_?
1571 \dX+ *stts_hoge
1572 \dX+ stts_s2.stts_yama
1573
1574 set search_path to public, stts_s1;
1575 \dX
1576
1577 create role regress_stats_ext nosuperuser;
1578 set role regress_stats_ext;
1579 \dX
1580 reset role;
1581
1582 drop table stts_t1, stts_t2, stts_t3;
1583 drop schema stts_s1, stts_s2 cascade;
1584 drop user regress_stats_ext;
1585 reset search_path;
1586
1587 -- User with no access
1588 CREATE USER regress_stats_user1;
1589 GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
1590 SET SESSION AUTHORIZATION regress_stats_user1;
1591 SELECT * FROM tststats.priv_test_tbl; -- Permission denied
1592
1593 -- Attempt to gain access using a leaky operator
1594 CREATE FUNCTION op_leak(int, int) RETURNS bool
1595     AS 'BEGIN RAISE NOTICE ''op_leak => %, %'', $1, $2; RETURN $1 < $2; END'
1596     LANGUAGE plpgsql;
1597 CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
1598                      restrict = scalarltsel);
1599 SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
1600 DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
1601
1602 -- Grant access via a security barrier view, but hide all data
1603 RESET SESSION AUTHORIZATION;
1604 CREATE VIEW tststats.priv_test_view WITH (security_barrier=true)
1605     AS SELECT * FROM tststats.priv_test_tbl WHERE false;
1606 GRANT SELECT, DELETE ON tststats.priv_test_view TO regress_stats_user1;
1607
1608 -- Should now have access via the view, but see nothing and leak nothing
1609 SET SESSION AUTHORIZATION regress_stats_user1;
1610 SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak
1611 DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak
1612
1613 -- Grant table access, but hide all data with RLS
1614 RESET SESSION AUTHORIZATION;
1615 ALTER TABLE tststats.priv_test_tbl ENABLE ROW LEVEL SECURITY;
1616 GRANT SELECT, DELETE ON tststats.priv_test_tbl TO regress_stats_user1;
1617
1618 -- Should now have direct table access, but see nothing and leak nothing
1619 SET SESSION AUTHORIZATION regress_stats_user1;
1620 SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
1621 DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
1622
1623 -- Tidy up
1624 DROP OPERATOR <<< (int, int);
1625 DROP FUNCTION op_leak(int, int);
1626 RESET SESSION AUTHORIZATION;
1627 DROP SCHEMA tststats CASCADE;
1628 DROP USER regress_stats_user1;