Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Problem description
For very large tables, we sample very small fraction of table to collect histograms. But EITS statistics collection still reads O(table_size) data:
1. Column statistics: It does a full table scan and "flips a coin" for each record to see if it should contribute to the histogram.
2. Index statistics: It does a full index scan to compute index statistics n_distinct(index_prefix) for each prefix. Note that this computation ignores tuples with NULLs.
The Goal: collect all kinds of statistics with sampling
1. Column statistics
There are two options:
A. Reuse innodb's index sampling. Sample the clustered PK but look at the column values also.
B. Reuse the knowledge from MDEV-39491 about how to start a read in different parts of the index and so get a record sample from different parts of the index.
One could get into the questions like "if we sample pages and use all data on the page for the sample, we get a larger more data for the same amount of data read from disk. On the other hand, getting co-located data makes the sample non-random. Especially so when the data is of variable-size, so one page will give you one "long" record or many "short" ones.
Maybe we can just use one record from every page read? That's not the top efficiency but would still be much better than the current approach of reading everything...
2. Index statistics
InnoDB can collect index statistics with "genuine sampling", that is, it can read a few index pages and come up with an estimate.
After MDEV-19574, the estimate is/will treat NULL values properly.