project_push_down.md · zettadb/kunlunbase-doc-en-1x

# Project and Filter pushdown

## foreword

[The previous article described the query optimization process ( query optimization process](query_opt_process.md) ) of KunlunBase (Kunlun Database) , and this article describes the pushdown demonstration of Project and Filter.

## 1. Basic information of the test table

### 1.1 Test environment

This test demonstrates pushdown for projection and filter operations.

The database cluster in the test environment has a total of four data nodes (DN), configured as two shards (shard1 and shard2), and each shard node consists of a master node and a slave node (the two nodes of shard1 are data replication relationships, and the two nodes of shard2 Each node is also a data replication relationship, shard1 and shard2 store different shard data of the data table). As shown below: ![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/1.png)

The node information of the cluster environment can be displayed by the following statement

```json
select t1.name, t2.shard_id, t2.hostaddr, t2.port, 
t2.user_name, t2.passwd from pg_shard t1, pg_shard_node t2  
where t2.shard_id=t1.id;
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/2.png)

### 1.2 Table structure

The table structure of this test is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/3.png)

### 1.3 Fragment Information

Table Customer1 is partitioned by range based on the c_id field, and the corresponding partition tables are: `Customer1_1,Customer1_2,Customer1_3,Customer1_4。`

The four fragmented data are stored in two shards:

```json
select t1.nspname, t2.relname,t2.relshardid, t2.relkind
from pg_namespace t1 join pg_class t2 ont1.oid = t2.relnamespace 
where t2.relshardid != 0 and relkind='r' and relname like 
'%customer1%' order by t2.relname;
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/4.png)

### 1.4 Data Distribution

Data falls into different shard storages according to sharding rules

The data distribution of the four partitions is as follows: ![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/5.png)

## 2. Query process

### 2.1 Filtering

Filtering is the positioning of the query range. Through filtering, the number of reads and writes of data blocks can be reduced.

Execution process: After the computing node interprets the SQL statement, RemoteScan pushes the query statement down to the corresponding storage node for execution according to the fragmentation information of the query target table customer1 (filtering irrelevant fragments), and the storage node returns the query result to calculate node.

Computing nodes adopt an asynchronous operation mode to push down queries, and multiple storage nodes can execute them in parallel.

For the following statements, according to the shard key value range, the SQL statement pair is pushed down to the corresponding storage node ( shard2 ) (this is the first filter of the SQL execution engine).

```json
explain select c_id,c_first,c_middle from customer1
where c_id=4;
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/6.png)

The mark is the filtering operation in the execution plan.

If the query condition does not contain a shard keyword, RemoteScan will send the statement to all shard nodes, and filter the shard results according to the query condition.

```json
explain select c_id ,c_middle,c_data from customer1
where c_middle='OE';
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/7.png)

KunlunBase (Kunlun Database) supports the use of functions and various complex conditions in query conditions

```json
explain select count(*) from customer1 
where sqrt(c_discount)>0.5;
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/8.png)

### 2.2 Project projection

After the computing node interprets the SQL statement, according to the fragmentation information of the query target table customer1, RemoteScan pushes down the query statement to each storage node for execution, and the storage node returns some fields (projection) in the query result row to the computing node.

The Project operation reduces the actual number of fields returned to the computing node (only the fields required by the query are returned, and irrelevant fields will not be read into the memory of the computing node).

The execution process refers to the following figure:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/9.png)

```json
explain select c_id,c_first,c_middle from customer1；
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/10.png)

During the execution of the above statement, remoteScan only returns the fields c_id, c_first, c_middle data to the computing node.

KunlunBase supports functions on fields and projects with various complex conditions

```json
explain selectc_balance + c_ytd_payment  from customer1
where c_id =20;
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/11.png)

The storage node only returns the result of c_balance+c_ytd_payment to the computing node.

### 2.3 Benefits of Fragmentation

The distributed database transparently distributes the database on different storage nodes at the database layer, and the benefits brought by this are:

- Transparent to the application, that is, the application can access the data without any modification.
- Storage nodes read and project data to reduce the load on computing nodes (less CPU and memory consumption).
- Data is distributed on different storage nodes, which facilitates elastic expansion of IO capabilities of the system and avoids IO hotspot competition.
- Projection and filtering operations can reduce the read and write range of data blocks, which can improve query efficiency.

## 3. Performance comparison

Create a data table with exactly the same data (quantity) as customer1 in the same environment, and then execute the same query to compare the efficiency of the query.

### 3.1 Query to fragmented table

```json
select now(); select count(*)  from customer1 
where c_id between 4 and 10;select now();
```

The result is as follows: ![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/12.png)

Time-consuming: 0.05s

### 3.2 Querying non-fragmented tables

Create table:

```json
CREATE TABLEcustomer1temp  (like  customer1);
insert intocustomer1temp select * From  customer1
```

The result is as follows:

![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/13.png)

Query time:

```json
select now(); select count(*) from customer1temp  
where c_idbetween 4 and 10 ;select now();
```

The result is as follows: ![img](http://doc.kunlunbase.com/Project%20%E5%92%8C%20Filter%20%E4%B8%8B%E6%8E%A8/14.png)

Time-consuming: 0.69s

## in conclusion

Through the use of Project and Filter, table fragmentation can improve the efficiency of data query.

# END

zettadb/kunlunbase-doc-en-1x

简介

发行版

贡献者

近期动态

zettadb/kunlunbase-doc-en-1x .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

zettadb/kunlunbase-doc-en-1x