"Hybriddb Performance Optimization · Several ways to implement Count distinct

Original address: Hybriddb Performance Optimization · Several implementation methods of Count distinct

HYBRIDDB is an MPP analytic database developed by Ali based on Greenplum, and Greenplum itself is based on PostgreSQL.

Thus, Hybriddb's optimization ideas and means will inevitably be affected and limited by PostgreSQL.

The final optimization of the statements in this paper has several different plans, and its optimized statements are simplified to form

Select COUNT (Distinct C1) from the T Group by C2;

This statement is implemented under HYBRIDDB:

1. Each server groups itself, computes the count (distinct);
2. Redistribute the previous results in the grouped columns;
3. Each server is calculated two times according to the data received;
4. Collect totals to a server to get the final result.

In this paper, the optimization points are concentrated in the way of grouping,

a), sorting + grouping;

b), hash grouping;

c), Orca optimization method, with a#, the improvement lies in the first order of the items are different.

a#, b#, C # Plan summary in the original

a),

Scan (Columnar scan + Append), sort (category), Group by (category), redistribute, category Group by (category), Sort, Gather

b),

Scan (Columnar scan + Append), group by (hash (Category,actionid)), Redistribute (category), Group by (Hash (cat Egory, Acitonid), Group by (category), Sort, Gather

c),
Scan (Dynamic Scan), sort (category, ActionId), Redistribute, Group by (category)-&G T Group by (category), Sort, Gather

Personally, this article refers to the optimization and MPP relationship is not very small, under the stand-alone may also be able to get similar different plans.

Perhaps it is limited by the data distribution characteristics, data volume and other factors, MPP under the data distribution, summary of motion optimization is not reflected here.

"Hybriddb Performance Optimization · Several ways to implement Count distinct