As mentioned in the previous share, LXDB is essentially an extension of PGSQL, replacing PG’s underlying data structure. This practice is based on the investigation and solution of the problem that the PG partition table cannot be statistically pushed down.

Now, let’s start our practice sharing:

First, background

Recently, due to customer demand, we added partitioning functionality to LXDB. However, after the function was launched, we found that group statistics such as count(*) and group by did not push the calculation down to the LXDB layer for calculation, but returned all data to the PG layer for calculation. Due to different data formats, there is a difference of at least two orders of magnitude in performance between statistics at the LXDB layer and data thrown to the PG layer. Therefore, in order to improve the statistics speed, we need to further investigate why the Postgres partition table does not sink the count(*) and group by group statistics into the LXDB layer.

Here is a brief introduction to LXDB. LXDB is essentially an extension of Postgres, which is equivalent to replacing the underlying data structure of Postgres. LXDB is used to solve the drawbacks of slow entry of original PG multi-index data into the database, poor update efficiency, and weaknesses in point-searching, full-text retrieval, statistical analysis and other scenarios. So that PG in the case of single machine can achieve 10 billion data millisecond level query statistical response.

Second, partition table build table

LXDB as a flagship lightweight database products, the overall operation command is very convenient, only need three steps to build a table.

1. Create a partition table

CREATE TABLE myn (
    ukey text,
    i1 integer,
    i2 integer,
    txt1 text,
    txt2 text
)
PARTITION BY RANGE (i1);
Copy the code

2. Add a partition

CREATE FOREIGN TABLE myn1 PARTITION OF myn FOR VALUES FROM (1000) TO (2000)  SERVER lxdb options(store 'ios');
CREATE FOREIGN TABLE myn2 PARTITION OF myn FOR VALUES FROM (2000) TO (3000)  SERVER lxdb options(store 'ios');
CREATE FOREIGN TABLE myn3 PARTITION OF myn FOR VALUES FROM (3000) TO (4000)  SERVER lxdb options(store 'ios');
CREATE FOREIGN TABLE myn4 PARTITION OF myn FOR VALUES FROM (4000) TO (5000)  SERVER lxdb options(store 'ios');
Copy the code

3. Insert data

INSERT INTO myn(ukey,i1, i2, txt1,txt2) VALUES ('1111',1500, 2, 'txt2', 'txt2');
INSERT INTO myn(ukey,i1, i2, txt1,txt2) VALUES ('1112',2500, 4, 'txt1', 'txt2');
INSERT INTO myn(ukey,i1, i2, txt1,txt2) VALUES ('1113',3500, 6, 'txt2', 'txt2');
INSERT INTO myn(ukey,i1, i2, txt1,txt2) VALUES ('1114',4500, 8, 'txt1', 'txt2');
Copy the code

Problem recurrence, analysis and troubleshooting

1. Problem recurrence

The first is to execute the query:

And then intercept the SQL actually delivered in LXDB.

As can be seen from the intercepted SQL, the system violently returned all data to PG in group statistics, resulting in poor performance of group statistics.

2. Start debugging

First, debug the stack for normal grouping statistics through GDB:

After entering the GDB postgres 15627 command, the lxdbGetForeignUpperPaths in the red box above is the processing function for group statistics in LXDB.

Based on this stack, we know that the function call stack is analyzed layer by layer based on the functions in the stack. Finally, we find that the call stack difference is in the create_ordinary_grouping_paths method (see figure below).

Then, through the PG source code, we analyzed the _create_ordinary_grouping_paths_ function found in the stack for grouping statistics.

We can see a problem with the execution logic of the partitioned table (see figure below). The PARTITIONWISE_AGGREGATE_PARTIAL variable causes the partitioned table to return prematurely.

Following this question, let’s examine the meaning of the PARTITIONWISE_AGGREGATE_PARTIAL variable (see figure below) :

But to be honest, I don’t really understand what it means here, so I have to keep looking at the assignment. Set enable_partitionWISE_aggregate = ON; set enable_partitionWISe_aggregate = ON;

3. Continue verification. The problem is resolved

After resetting set enable_PARTItionWISE_AGGREGATE, let’s test it again:

As you can see, by tweaking the Settings **, the grouping statistics are indeed pushed down to the LXDB layer for calculation, and the problem has been resolved. **

Partition wiseaggregation allows partitioning tables that are executed separately for each partition to be grouped or aggregated. If the GROUP BY clause does not include partitioning keys, only partial aggregation can be performed on a per-partition basis, and final processing must be performed later. Because partitionWise grouping or aggregation can consume a lot of CPU time and memory during planning, it is set to off by default.