TRUNCATE TABLE ndistinct;

-- under-estimates when using only per-column statistics

INSERT INTO ndistinct (a, b, c, filler1)

SELECT mod(i,50), mod(i,51), mod(i,32),

cash_words(mod(i,33)::int::money)

FROM generate_series(1,5000) s(i);

SELECT mod(i,13), mod(i,17), mod(i,19),

cash_words(mod(i,23)::int::money)

FROM generate_series(1,1000) s(i);

ANALYZE ndistinct;

SELECT s.stxkind, d.stxdndistinct

FROM pg_statistic_ext s, pg_statistic_ext_data d

WHERE s.stxrelid = 'ndistinct'::regclass

AND d.stxoid = s.oid;

stxkind |stxdndistinct

---------+------------------------------------------------------------

{d,f,m} | {"3, 4":2550, "3, 6":800, "4, 6":1632, "3, 4, 6":5000}

stxkind | stxdndistinct

---------+----------------------------------------------------------

{d,f,m} | {"3, 4":221, "3, 6":247, "4, 6":323, "3, 4, 6":1000}

(1 row)

-- correct estimates

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');

estimated | actual

-----------+--------

2550|2550

221| 221

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');

estimated | actual

-----------+--------

5000 |5000

1000 |1000

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');

estimated | actual

-----------+--------

5000 |5000

1000 |1000

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');

estimated | actual

-----------+--------

1632|1632

323| 323

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');

estimated | actual

-----------+--------

500 |50

200 |13

(1 row)

DROP STATISTICS s10;

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');

estimated | actual

-----------+--------

500 |2550

100 | 221

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c');

estimated | actual

-----------+--------

500 |5000

100 |1000

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d');

estimated | actual

-----------+--------

500 |5000

200 |1000

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d');

estimated | actual

-----------+--------

500 |1632

200 | 323

(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, d');

estimated | actual

-----------+--------

500 |50

200 |13

(1 row)

-- functional dependencies tests

CREATE INDEX fdeps_abc_idx ON functional_dependencies (a, b, c);

-- random data (no functional dependencies)

INSERT INTO functional_dependencies (a, b, c, filler1)

SELECT mod(i,23), mod(i,29), mod(i,31), i FROM generate_series(1,5000) s(i);

SELECT mod(i,5), mod(i,7), mod(i,11), i FROM generate_series(1,1000) s(i);

ANALYZE functional_dependencies;

SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');

estimated | actual

-----------+--------

8| 8

29|29

(1 row)

SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');

estimated | actual

-----------+--------

1 |1

3 |3

(1 row)

-- create statistics

SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');

estimated | actual

-----------+--------

8| 8

29|29

(1 row)

SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'' AND c = 1');

estimated | actual

-----------+--------

1 |1

3 |3

(1 row)

-- a => b, a => c, b => c

INSERT INTO ndistinct (a, b, c, filler1)

SELECT mod(i,50), mod(i,51), mod(i,32),

cash_words(mod(i,33)::int::money)

FROM generate_series(1,5000) s(i);

SELECT mod(i,13), mod(i,17), mod(i,19),

cash_words(mod(i,23)::int::money)

FROM generate_series(1,1000) s(i);

ANALYZE ndistinct;

INSERT INTO functional_dependencies (a, b, c, filler1)

SELECT mod(i,23), mod(i,29), mod(i,31), iFROM generate_series(1,5000) s(i);

SELECT mod(i,5), mod(i,7), mod(i,11), iFROM generate_series(1,1000) s(i);

ANALYZE functional_dependencies;