How the Columnstore Works

Creating Efficient Columnstore Queries

Queries against tables with a columnstore index in SingleStore can take advantage of five characteristics of a columnstore index:

1. All queries will be able to take advantage of the fact that only the column segments containing columns referenced in the query will need to be scanned, as well as the fact that the columnstore’s compression causes less data to need to be scanned. Taking the above table as an example. The query SELECT SUM(Qty) FROM Products; will only need to scan the three Qty column segments, each of which only contain a single value due to the compression.

2. Some queries can be performed simply by reading the in-memory metadata for column segments referenced in the query. As an example, the query SELECT COUNT(*) FROM Products; will only need to read the row count and delete bitmask for all row segments to produce its result, completely eliminating the need to read column segments from disk. Queries that use the MIN or MAX aggregates can eliminate the need to read column segments from disk if no delete has been performed against the column segment.

3. Some queries can reduce the number of segments that need to be read from disk by eliminating these segments based on the segment metadata (min and max values for the segment). The efficiency of this method depends on what percentage of segments can actually be eliminated.

   • For queries that filter on the key columns of the columnstore index, segment elimination is typically very efficient as segments within each row segment will not cover overlapping value ranges. For example, in the above table the query SELECT AVG(Price), AVG(Qty) FROM Products WHERE Price BETWEEN 1 AND 10; will eliminate all segments except row segment: #1 column segment: Price 4-15 and row segment: #1 column segment: Qty 2-2 which will be scanned.

   • For queries that filter on the non-key columns that don’t correlate with the key columns, segment elimination can be much less valuable as segments within each row segment can cover overlapping value ranges. For example, in the above table, the query SELECT AVG(Price) FROM Products WHERE Color = 'Red'; will be forced to scan all segments for the Price and Color columns as no segment can be eliminated when the value Red is contained in all segments of the Color column.

4. Queries that join tables on columns that are the index columns of a columnstore index can be performed very efficiently through the use of a merge join algorithm allowing the join to be performed by simply scanning two segments that need to be joined in lock-step.

5. Certain types of data allow filters and group-by operations to be performed without decompressing data from its serialized-for-disk format. This greatly improves performance by reducing the amount of data that need to be processed, especially when the cardinalities of the involved columns are low. This optimization is only performed in cases when execution run time would be improved. See Encoded Data in Columnstores for more information.

6. Queries with selective filters use subsegment access. These queries seek into column segments to read the needed values, rather than scanning whole segments. For a columnstore table MyTable, the query SELECT Field1, Field2 FROM MyTable WHERE Field1 > 50 uses subsegment access. Selective filters with multiple conditions, such as SELECT Field1, Field2 FROM MyTable WHERE Field1 > 50 AND Field2 > 100, also use subsegment access.

7. Queries using equality filters can take advantage of hash indexes. See an example. The previous note applies to these queries, since equality filters are often selective filters.

Finding the Size of a Column Group Index

The size (in bytes) of a column group index can be found in the MV_COLUMNSTORE_FILES view as follows:

CREATE TABLE colgrpsz_1 (a INT, b INT PRIMARY KEY, COLUMN GROUP(*));

INSERT INTO colgrpsz_1 VALUES (1,2),(6,4),(36,8);  

Run the OPTIMIZE TABLE FULL command on any table you want to know the column group index size on. This will create a more accurate reflection of the physical storage of the table's data. Please note, that this operation can be resource-intensive and time-consuming, particularly for large tables. Therefore, it should be scheduled during periods of low system usage to minimize the impact on system performance.

OPTIMIZE TABLE colgrpsz_1 FULL;

Filter the view for the COLUMN_GROUP and only information about the column group index will be returned. If there are column group indexes on other tables, they will be displayed in the following query as well.

SELECT * FROM information_schema.MV_COLUMNSTORE_FILES 
    WHERE FILE_TYPE = 'COLUMN_GROUP; 

+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+
| DATABASE_NAME | TABLE_NAME | FILE_TYPE    | FILE                  | HOST      | PORT | NODE_ID | PARTITION | SIZE | CREATION_TIME       | LSN | TERM | CHECKSUM   |
+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+
| test1         | colgrpsz_1 | COLUMN_GROUP | blobs/5006/0/0/173_51 | 127.0.0.1 | 3307 |       2 |         2 |   33 | 2024-05-06 11:43:05 |  51 |  173 | 1230141913 |
| test1         | colgrpsz_2 | COLUMN_GROUP | blobs/5006/0/0/173_66 | 127.0.0.1 | 3307 |       2 |         2 |   33 | 2024-05-06 13:39:52 |  66 |  173 | 1230141913 |
| test1         | colgrpsz_1 | COLUMN_GROUP | blobs/5002/0/0/174_52 | 127.0.0.1 | 3307 |       2 |         3 |   47 | 2024-05-06 11:43:05 |  52 |  174 | 2716425258 |
| test1         | colgrpsz_2 | COLUMN_GROUP | blobs/5002/0/0/174_67 | 127.0.0.1 | 3307 |       2 |         3 |   47 | 2024-05-06 13:39:52 |  67 |  174 | 2716425258 |
+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+

The MV_COLUMNSTORE_FILES view can be filtered to search for a specific table, the size summed to see the total column group index size per table, etc.

SELECT * FROM information_schema.MV_COLUMNSTORE_FILES 
    WHERE FILE_TYPE = 'COLUMN_GROUP' AND TABLE_NAME = 'colgrpsz_1';

+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+
| DATABASE_NAME | TABLE_NAME | FILE_TYPE    | FILE                  | HOST      | PORT | NODE_ID | PARTITION | SIZE | CREATION_TIME       | LSN | TERM | CHECKSUM   |
+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+
| test1         | colgrpsz_1 | COLUMN_GROUP | blobs/5006/0/0/173_51 | 127.0.0.1 | 3307 |       2 |         2 |   33 | 2024-05-06 11:43:05 |  51 |  173 | 1230141913 |
| test1         | colgrpsz_1 | COLUMN_GROUP | blobs/5002/0/0/174_52 | 127.0.0.1 | 3307 |       2 |         3 |   47 | 2024-05-06 11:43:05 |  52 |  174 | 2716425258 |
+---------------+------------+--------------+-----------------------+-----------+------+---------+-----------+------+---------------------+-----+------+------------+

SELECT DATABASE_NAME, TABLE_NAME, FILE_TYPE, SUM(SIZE) FROM information_schema.MV_COLUMNSTORE_FILES  
    WHERE FILE_TYPE = 'COLUMN_GROUP' AND TABLE_NAME = 'colgrpsz_1'    
    GROUP BY DATABASE_NAME, TABLE_NAME, FILE_TYPE;

+---------------+------------+--------------+-----------+
| DATABASE_NAME | TABLE_NAME | FILE_TYPE    | SUM(SIZE) |
+---------------+------------+--------------+-----------+
| test1         | colgrpsz_1 | COLUMN_GROUP |        80 |
+---------------+------------+--------------+-----------+