Guide to Read Data Efficiently in LeanXcale

To retrieve payments from a specific source account for a designated month, the following query will be executed:

The first observation is that the query is performing slowly. To understand the underlying reasons for this inefficiency, it is essential to examine the query execution plan, which reveals the operations the database is performing.

To obtain the query plan, the following command should be executed:

The query plan will provide insights into the execution strategy, including any table scans, join methods, or index usage, allowing us to identify potential performance bottlenecks.

The query plan is represented as a tree structure (or sometimes a list). Upon examining the query plan, we observe the following. At the lowest level, we encounter a Scan operator, specifically KiviPKTableScanRel. This indicates that a full table scan is being performed.

We know that it is indeed a full scan because there are no constraints indicated, such as minimum or maximum limits (i.e., no min/max or mini/maxi values specified). This lack of limits indicates that the query is evaluating all records in the table, which results to the observed slowness of the query execution.

How we can solve the problem? Basically, we have to provide an index to access efficiently rows for a particular origin bank account and month, one alternative is to use the primary key to have fast access.

To incorporate the source account as part of the primary key, the table should be created as follows:

Upon reviewing the query plan for the query, we find the following details:

In this execution plan, we observe that the scan operator now includes limits (mini:[?0], maxi:[?0]), indicating that it is performing a range query rather than a full table scan.

Upon closer examination of the limits, we note that the first parameter (?0) is utilized as both the minimum and maximum limit. The additional 'i' in mini and maxi signifies that these limits are inclusive.

After introducing the primary key, we still observe that the query execution time remains suboptimal. This can be attributed to the lack of proper partitioning and distribution of the table across the storage engines (kvds).

With four physical cores and four kvds, it is essential to utilize all available CPU resources for query execution. By default, a newly created table is not partitioned or distributed, meaning that all data is handled by a single storage engine (kvds). Since each kvds is assigned to one physical core, this configuration effectively limits the query to the processing power of a single core, whereas we aim to leverage all four cores for improved performance.

To enhance performance, it is standard practice to partition and distribute data according to the primary key for regular tables. To achieve this, we need to add the PARTITION BY clause to the CREATE TABLE statement, specifying the dimension by which the table will be partitioned, in this case, the primary key.

Furthermore, to distribute the table across four kvds, we must define three split points (4 - 1 = 3), which will create four ranges of primary keys. The split points should be chosen to ensure that the resulting partitions are as evenly distributed as possible. For instance, assuming that the primary key is uniformly distributed, we can select the split points based on the prefixes: '25', '50', and '75'. This approach assumes that all primary keys have the same number of digits (e.g., 10), leading to an even distribution of rows across the partitions.

The resulting CREATE TABLE statement with partitioning is as follows:

Now let us assume we also need to query by destination account. We would perform the query as follows:

Again we realize the query is slow. To diagnose the issue, we should analyze the query plan:

In this plan, we observe that the scan operator KiviPKTableScanRel is scanning the table using the primary key (PK). However, there are no specified limits (min/max), indicating that the operation is a full table scan. This is inefficient, especially for large datasets.

Furthermore, we notice that the WHERE clause has been translated into a filter condition: AND(=($3, ?0). This comparison checks the third column (destination account) against the first parameter (?0), which is applied to all rows in the table. As a result, this approach leads to unnecessary evaluations across the entire dataset, contributing to the slowness.

To address this performance issue, we need to implement a faster access method. Given that we already have a meaningful primary key, the most effective solution is to introduce a secondary index on the destination account. This will facilitate more efficient queries, allowing the query to directly target relevant records without scanning the entire table.

By adding a secondary index on the dst_acct column, we can significantly improve query performance for conditions involving that column:

After creating the index, let’s examine the updated query plan:

In this plan, we observe two key improvements. First, the scan operator has changed to KiviIndexTableScanRel, indicating that the query is now utilizing the secondary index rather than performing a primary key scan. This is a significant improvement, since it can traverse just the rows with the destination account of interest. Second, the presence of limits on the scan operator (mini:[?0], maxi:[?0]) indicates that this is now an index range query. The query efficiently uses the destination account value passed as a parameter (?0) to limit the range of scanned records. These enhancements result in a more efficient data retrieval process, allowing the database to quickly access only the relevant rows based on the destination account and the specified filtering criteria. Overall, the addition of the secondary index has substantially improved the query’s performance.

Let’s examine a typical aggregation query for payments that groups data by destination account:

Despite its intent, this query executes slowly. To diagnose the performance issues, we should analyze the query plan:

In this plan, we observe that, first, the scan is a full scan, indicated by the absence of limits (min/max), meaning that the entire table is being evaluated. This is inefficient for large datasets. Second, the aggregation is computed as the scan traverses each row, further contributing to the slow performance. To enhance the speed of the query, we can utilize LeanXcale Online Aggregates. These are efficient materialized views of aggregate queries that are efficiently updated in real time (without creating any contention due to concurreency control as in traditional databases), providing significant performance benefits.

The online aggregate can be created as follows:

After creating the online aggregate, let’s examine the updated query plan:

In this plan, we notice two significant improvements. First, the scan operator has changed to KiviDerivedTableRangeScanRel, indicating that the query is now utilizing a derived table instead of performing a primary key scan. This is more efficient for aggregation queries since only reads the aggregated results instead of the detailed data of the parent table. Second, the presence of limits on the scan operator (min and max) indicates that this is now a derived table range query, effectively using the year and month parameters (?0 and ?1) to restrict the scanned records.

It’s important to note that when we create an online aggregate, a derived table is established that is associated with the parent table containing the online aggregate. This derived table has a primary key comprising the columns of the GROUP BY clause—in this case, year, month, and destination account. This structure allows for efficient querying and retrieval of aggregated data, resulting in significantly improved performance.

Now, let’s examine another similar query that aggregates data by year instead of by month:

In this query, we are retrieving totals for a specific year, rather than for individual months. By analyzing the query plan, we find:

From this plan, we observe the following. The query is effectively leveraging the previously created online aggregate. This is possible because the online aggregate maintains a coarser granularity (monthly aggregates) that can be aggregated further to compute yearly totals. Derived Table Scan: The operator is a KiviDerivedTableRangeScanRel, indicating that the query reads from the monthly aggregates stored in the online aggregate to compute the yearly aggregates. This approach is considerably more efficient than recalculating the totals from scratch across the entire dataset. By using the existing monthly aggregates, the query minimizes the amount of data processed, resulting in faster execution times and improved performance overall.

Let’s consider a top-k aggregation query designed to retrieve the top 10 destination accounts by total amount transferred in a specific month:

This query aims to fetch the top 10 destination accounts along with their corresponding payments for a specific month. The query plan reveals that it indeed utilizes the online aggregate, as indicated by the derived table scan. However, despite this optimization, the query executes slowly.

By analyzing the query plan:

The plan indicates a hash join between the online aggregate (the right side of the join) and the parent table (the left side). The left side of the join is executing a full scan, meaning every row in the parent table is being evaluated against the hash table created from the aggregated results. This results in inefficiency, especially given that only a limited number of rows are ultimately selected.

The optimizer’s choice of this suboptimal plan stems from a lack of fresh statistics about the underlying data. The optimizer relies on these statistics to make informed decisions about the most efficient execution strategy. In this case, because statistics were not run, the optimizer could not accurately assess the data distribution, leading it to select a less efficient plan. To rectify this issue, we need to gather updated statistics:

After running the statistics, let’s review the new query plan:

The plan now employs a correlate join, which is more efficient as it correlates the derived table directly with the index scan. The left side now utilizes an index range scan for each row in the derived table. The scan operates with the same value for both limits, optimizing the retrieval of relevant rows. This new plan significantly enhances query performance by minimizing unnecessary evaluations and leveraging the available online aggregate effectively.

Dates can be stored using various SQL data types, including timestamp, date, or even integers. Below are examples demonstrating how to create online aggregates for each case.

For a timestamp column as in the following schema:

To efficiently aggregate payments by day and by month, you can define the following online aggregate:

It should be noted, that since aggregations can be large, they can also be partitioned as large tables, in this case there is a partition per month.

If the table uses a date column instead, the schema will look like this:

The corresponding online aggregate for monthly data aggregation would be:

For a table where the date is encoded as an integer (i.e., in the format DDMMYY), the schema is structured as follows:

The online aggregate for this setup can be created using: