Understanding Sort Key Selection
On this page
Sort Key
The sort key is an index that groups rows of columnstore tables into logical segments, where each segment contains data for many rows.BLOB
data type.BLOB
data type.
To set a sort key, you use SORT KEY()
syntax.SORT KEY()
.
CREATE TABLE t1(col1 int, SORT KEY( ));EXPLAIN SELECT * FROM t1;
+---------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 |
| Project [t1.col1] |
| ColumnStoreScan test8.t1, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore |
+---------------------------------------------------------------------------------------------------------+
The sort key order can be specified as ascending (SORT KEY(index_
) or descending (SORT KEY(index_
).SORT KEY()
in reverse order to its sort order.
CREATE TABLE t1(col1 int, SORT KEY(col1 DESC));EXPLAIN SELECT * FROM t1 ORDER BY col1 DESC;
+------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------+
| GatherMerge [remote_0.col1 DESC] partitions:all alias:remote_0 |
| Project [t1.col1] |
| OrderedColumnStoreScan test1.t1, KEY col1 (col1 DESC) USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
EXPLAIN SELECT * FROM t1 ORDER BY col1;
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------+
| GatherMerge [remote_0.col1] partitions:all alias:remote_0 |
| Project [t1.col1] |
| Sort [t1.col1] |
| ColumnStoreScan test1.t1, KEY col1 (col1 DESC) USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore |
+-----------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
Note
KEY() USING CLUSTERED COLUMNSTORE
is a legacy syntax that is equivalent to SORT KEY()
.SORT KEY()
.
As with any other SingleStore table, we suggest you define a shard key to explicitly control the data distribution within the cluster.id
since sharding on a high cardinality identifier column generally allows for more even distribution and prevents skew.
CREATE TABLE people (id INT AUTO_INCREMENT,user VARCHAR(24),first VARCHAR(24),last VARCHAR(24),SORT KEY (user),SHARD KEY (id));
Furthermore, setting the shard key and the sort key on the same column improves data compression.id
column.
Segment Elimination
The single most important consideration for columnstore tables is setting a sort key.
Indexing cuts down on the number of rows scanned when a select query with a where clause is executed.
The segment size also impacts query execution with respect to the sort key.
Column segments typically contain on the order of tens of thousands of rows.people
table (the segment size is 3 rows for readability):
Segment #1 of 3 - user
values aa - jl
![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt4f3354d1dc18a22f/66db607064c83a66c971a13a/Column_Segment_1_SS_tone-65gR87.png?width=850&disable=upscale&auto=webp)
Segment #2 or 3 - user
values jm - rl
![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blt8db4c65c825f6fd2/66db60aa0861db357035f1c1/Column_Segment_2_SS_tone-4wCWOJ.png?width=850&disable=upscale&auto=webp)
Segment #3 of 3 - user
values rm - zz
![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltf1a69f5c9a75f9e3/66db60c0aa3bbaf06acea67b/Column_Segment_3_SS_tone-25FpW8.png?width=850&disable=upscale&auto=webp)
If a query is searching for a person whose first name starts with an "a", then only the first segment is scanned.user
values are "jm - zz".
Using Multiple Sort Keys on a Table
Using a table that only stores integers, this is how the data is stored when only one column is set as the sort key.
CREATE TABLE INTEGERS (A int,B int,SORT KEY (A));
If only integers 1 - 10 are inserted into both columns, the segmentation would look like this if we review only values 1 - 3 for column A:
![Graphic illustrating columnstore segments and the columns with values assigned to each segment.](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltaad01dc11f8e82a3/66db60a9b2ce885d7c4ef4ac/Clustered_Columnstore_Key_A_-qA5VSJ.png?width=850&disable=upscale&auto=webp)
The values in column A are segmented in order.
If this same INTEGERS
table was created with both columns as sort keys, here's the syntax:
CREATE TABLE INTEGERS (A int,B int,SORT KEY (A,B));
Again, if only integers 1 - 10 are inserted into both columns, the segmentation would look like this if we review only values 1 - 3 for column A:
![Graphic illustrating columnstore segments and the columns with values assigned to each segment.](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/bltaa4423eb527f0868/66db609ec646020502a2b03f/Clustered_Columnstore_Key_A_B_-tVvBdB.png?width=850&disable=upscale&auto=webp)
The values in column A are segmented in order.
Creating multiple sort keys on a table can decrease the amount of scanning needed when querying a table.
A shard key can also be set on columnstore tables to further improve performance.
Questions to Ask When Choosing Sort Keys
-
Is the data always filtered by some column (e.
g. insert timestamp or event type)? Ensure that the common columns for all queries are in the sort key to improve segment elimination. -
Is the data generally inserted in order by some column (e.
g. insert timestamp)? It’s best to put that column first in the sort key to minimize the amount of work required by the background columnstore segment merger. -
Does one column in your key have a higher cardinality than the other? It’s best to put the lowest cardinality columns first to increase the likelihood that segment elimination will be able to affect later columns.
Managing Columnstore Segments
A columnstore table will have the best performance if the rows in the table are in global sorted order across all the row segments.
SingleStore uses an advanced algorithm that allows it to maintain the order as close to sorted as possible, while data is being ingested or updated.background merger
and is constantly running in the background if the order of the row segments can be improved.
Background merger
is optimistic, in that if at any point it tries to move around data which is also being changed by a concurrent UPDATE or DELETE query, it will discard all the work it has done so far and start over.pessimistic
merger, which locks the row segments it is currently processing.pessimistic
merger by running an OPTIMIZE TABLE
command.
SingleStore uses a concept of a sorted row segment group
to describe a set of row segments that are sorted together.SORT KEY
of that row are smaller than those of the other row.
If the data had a perfect global order, it would consist of a single sorted row segment group.background merger
is to reorganize the rows among row segments in such a way that the number of sorted row segment groups is as small as possible.
To inspect the current state of the sorted row segment groups of a particular table, run the SHOW COLUMNAR MERGE STATUS FOR <table_
command:
SHOW COLUMNAR MERGE STATUS FOR groups;
+----------------------------------------------------------------------------+
| Merger | State | Plan | Progress | Partition |
+----------------------------------------------------------------------------+
| (Current groups) | NULL | 741,16,1 | NULL | 0 |
| (Current groups) | NULL | 782,20 | NULL | 1 |
| (Current groups) | NULL | 701,40,5 | NULL | 2 |
| (Current groups) | NULL | 326,207,123,37,21,19,17 | NULL | 3 |
+----------------------------------------------------------------------------+
Let’s look closely at the first row of the result.0
has three sorted row segment groups, one consists of 741 row segments, one consists of 16 row segments, and one consists of a single row segment - a total of 758 row segments.
SELECT * FROM groups WHERE user_group = 15;
The very first sorted row segment group will have at most one row segment that contains rows with user_
equal to 15, unless user_
is on the boundary of two row segments, or if there is a large data skew and several row segments consist only of rows with user_
.
Now take a look at the sorted row segment groups on partition 3
.SELECT
query like the one shown above will result in materializing eight row segments.background merger
is enabled, and no workload is running concurrently, within several seconds this partition would get optimized.background merger
might fall behind.
OPTIMIZE TABLE groups;
If we run SHOW COLUMNAR MERGE STATUS
while OPTIMIZE TABLE
is being executed, we might see the manual merger in action:
SHOW COLUMNAR MERGE STATUS FOR groups;
+--------------------------------------------------------------------------------+
| Merger | State | Plan | Progress | Partition |
+--------------------------------------------------------------------------------+
| (Current groups) | NULL | 741,16,1 | NULL | 0 |
| (Current groups) | NULL | 782,20 | NULL | 1 |
| (Current groups) | NULL | 701,40,5 | NULL | 2 |
| (Current groups) | NULL | 326,207,123,37,21,19,17 | NULL | 3 |
| Manual Merger | Working | 326+207+123+37+21+19+17 | 53.12% | 3 |
+--------------------------------------------------------------------------------+
What this new row indicates is that there is a manual merger running on partition 3 and that at this time it has done 53.
of the work.
When the merger is done, the table now is in a better shape:
SHOW COLUMNAR MERGE STATUS FOR groups;
+------------------------------------------------------------+
| Merger | State | Plan | Progress | Partition |
+------------------------------------------------------------+
| (Current groups) | NULL | 741,16,1 | NULL | 0 |
| (Current groups) | NULL | 782,20 | NULL | 1 |
| (Current groups) | NULL | 701,40,5 | NULL | 2 |
| (Current groups) | NULL | 730,20 | NULL | 3 |
+------------------------------------------------------------+
Note that at no point were any of the partitions merged into a single sorted row segment group in this example.SELECT
performance, it is possible to run a manual command that merges data on each partition into a single sorted row segment group:
OPTIMIZE TABLE groups FULL; SHOW COLUMNAR MERGE STATUS FOR groups;
+---------------------------------------------------------+
| Merger | State | Plan | Progress | Partition |
+---------------------------------------------------------+
| (Current groups) | NULL | 758 | NULL | 0 |
| (Current groups) | NULL | 802 | NULL | 1 |
| (Current groups) | NULL | 746 | NULL | 2 |
| (Current groups) | NULL | 750 | NULL | 3 |
+---------------------------------------------------------+
At this time, any highly selective SELECT
will materialize only one row segment per partition.
Important
Unlike OPTIMIZE TABLE <name>
, which takes the amount of time proportional to the size of recently loaded data, OPTIMIZE TABLE <name> FULL
always takes the amount of time in the order of magnitude of the size of the entire table, unless data in that table is already sorted.
When inserting a small number of rows into the columnstore table, an in-memory rowstore-backed segment is used to store the rows.background flusher
periodically will flush these rows to disk.OPTIMIZE TABLE <table_
.
OPTIMIZE TABLE t FLUSH;
Last modified: March 8, 2024