Statistics Information Statistic #

The current ByteHouse self-developed query optimizer uses statistical information to enhance query performance. Currently, it supports users manually creating stats to collect statistics from tables or change query designs to enhance query performance. Future support for automatic collection and update capabilities will be provided. The supported syntax is as follows:

1. Create Statistics

CREATE STATS [IF NOT EXISTS] (<db>.* | ALL | [<db>.]<table>['(' <col1>, <col2>, ..., <coln> ')']) 
        [WITH (FULLSCAN | SAMPLE [<rows> ROWS] [<ratio> RATIO])] 

2. Show Statistics

SHOW (STATS|COLUMN_STATS) (<db>.* | ALL | [<db>.]<table>'('<col1>, <col2>, ..., <coln> ')' )

3. Delete Statistics

DROP STATS (<db>.* | ALL | [<db>.]<table>['(' <col1>, <col2>, ..., <coln> ')' ])

Execution Plan Explain Plan #

For data engineers, diagnosing issues through execution plans is a crucial troubleshooting method. For example, problems such as Join order, whether the statistical information is missing, whether the estimation is reasonable, and whether the pushdown is activated. ByteHouse provides the capability to display physical execution plans. It uses the Explain + Query method for performing analyses, which facilitates a more intuitive analysis and optimization of queries alongside the visualization of Enquiry Details via Query Profiler.

4. Generate physical execution plan:

EXPLAIN (QUERY Statement) ;

Use Example #

1. Create a database table

CREATE DATABASE sampledb;
CREATE TABLE sampledb.t1 
(
  id Int64,
  name Int64
) ENGINE = CnchMergeTree()
ORDER BY id;
CREATE TABLE sampledb.t2
(
  uid Int64,
  name Int64
) ENGINE = CnchMergeTree()
ORDER BY uid;

2. Insert sample data

INSERT INTO sampledb.t1 VALUES (1,'bob'),(2,'alice'),(3,'peter'),(4,'jay'),(5,'yiyang'),(6,'aurora');
INSERT INTO sampledb.t2 VALUES (1,'smith'),(2,'parker'),(3,'taylor'),(4,'davis'),(5,'joines');

3. Collect statistical information of the table

CREATE stats sampledb.t1;
CREATE stats sampledb.t2;

Show the following results

4. Show the statistical information of the table

SHOW stats sampledb.t1;

5. Generate an execution plan for the table

USE sampledb;
EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.uid);

Show the following information:

Gather Exchange est. 6 rows
└─ Projection est. 6 rows
│ Expressions: [id, name, uid], t2.name:=name_1
└─ Inner Join est. 6 rows
│ Condition: id == uid
├─ Local Exchange est. 6 rows
│ └─ ReadFromStorage 2100069045.sampledb.t1 est. 6 rows
│ Outputs: [id, name]
└─ Broadcast Exchange est. 5 rows
└─ ReadFromStorage 2100069045.sampledb.t2 est. 5 rows
Outputs: [uid], name_1:=name

6. Delete the statistical information of the table

DROP stats sampledb.t1;
DROP stats sampledb.t1;