Debian Codegraph @

mysql-test/suite/secondary_engine/inc/histogram.inc

raw
1 --source include/elide_costs.inc
2
3 --echo #
4 --echo # Test that histograms are used for optimizing queries that are
5 --echo # offloaded to a secondary storage engine.
6 --echo #
7
8 --disable_query_log
9 eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN';
10 --enable_query_log
11
12 CREATE TABLE t1(id INT PRIMARY KEY, x INT NOT NULL) SECONDARY_ENGINE MOCK;
13 INSERT INTO t1 VALUES
14 (1, 1), (2, 1), (3, 1), (4, 1), (5, 1),
15 (6, 1), (7, 1), (8, 1), (9, 2), (10, 2);
16
17 ALTER TABLE t1 SECONDARY_LOAD;
18
19 # Without histograms, all three queries get the same selectivity (10%).
20 ANALYZE TABLE t1;
21 --replace_regex $elide_costs
22 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
23 --replace_regex $elide_costs
24 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
25 --replace_regex $elide_costs
26 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
27
28 # With histograms, the selectivity varies with the constant used in
29 # the predicate.
30 ANALYZE TABLE t1 UPDATE HISTOGRAM ON x WITH 10 BUCKETS;
31 ANALYZE TABLE t1;
32 --replace_regex $elide_costs
33 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
34 --replace_regex $elide_costs
35 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
36 # The hypergraph optimizer estimates 0.01 rows when the value is not
37 # found in the histogram, whereas the old optimizer estimates 1 row.
38 --replace_regex $elide_costs
39 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
40
41 # Verify that the secondary storage engine is used for the queries
42 # (they all return empty results when offloaded to the mock engine).
43 SELECT * FROM t1 WHERE x = 1;
44 SELECT * FROM t1 WHERE x = 2;
45 SELECT * FROM t1 WHERE x = 3;
46
47 # Drop the histogram and lose the histogram statistics in the query plans.
48 ANALYZE TABLE t1 DROP HISTOGRAM ON x;
49 ANALYZE TABLE t1;
50 --replace_regex $elide_costs
51 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
52 --replace_regex $elide_costs
53 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
54 --replace_regex $elide_costs
55 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
56
57 # Recreate the histogram and get the good selectivity estimates back.
58 ANALYZE TABLE t1 UPDATE HISTOGRAM ON x WITH 10 BUCKETS;
59 ANALYZE TABLE t1;
60 --replace_regex $elide_costs
61 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 1;
62 --replace_regex $elide_costs
63 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 2;
64 # The hypergraph optimizer estimates 0.01 rows when the value is not
65 # found in the histogram, whereas the old optimizer estimates 1 row.
66 --replace_regex $elide_costs
67 EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE x = 3;
68
69 DROP TABLE t1;
70
71 --disable_query_log
72 UNINSTALL PLUGIN mock;
73 --enable_query_log