Clustering columns

How order impacts clustering restrictions

Because the database uses the clustering columns to determine the location of the data on the partition, you must identify the higher level clustering columns definitively using the equals (=) or IN operators. In a query, you can only restrict the lowest level using the range operators (>, >=, <, or <=).

CREATE TABLE numbers (
   key int,
   col_1 int,
   col_2 int,
   col_3 int,
   col_4 int,
   PRIMARY KEY ((key), col_1, col_2, col_3, col_4));

 key | col_1 | col_2 | col_3 | col_4
-----+-------+-------+-------+-------
 100 |     1 |     1 |     1 |     1
 100 |     1 |     1 |     1 |     2
 100 |     1 |     1 |     1 |     3
 100 |     1 |     1 |     2 |     1
 100 |     1 |     1 |     2 |     2
 100 |     1 |     1 |     2 |     3
 100 |     1 |     2 |     2 |     1
 100 |     1 |     2 |     2 |     2
 100 |     1 |     2 |     2 |     3
 100 |     2 |     1 |     1 |     1
 100 |     2 |     1 |     1 |     2
 100 |     2 |     1 |     1 |     3
 100 |     2 |     1 |     2 |     1
 100 |     2 |     1 |     2 |     2
 100 |     2 |     1 |     2 |     3
 100 |     2 |     2 |     2 |     1
 100 |     2 |     2 |     2 |     2
 100 |     2 |     2 |     2 |     3

(18 rows)

The database stores and locates the data using a nested sort order. The data is stored in hierarchy that the query must traverse:

{ “key” : “100”  { 
         “col_1” : “1”  {
                  “col_2” : “1” {
                            “col_3” : “1” { 
                                       “col_4” : “1”, 
                                       “col_4” : “2”, 
                                       “col_4” : “3”  },
                            “col_3” : “2” {
                                      “col_4” : “1”,
                                      “col_4” : “2”,
                                      “col_4” : “3”  } }, 
                  “col_2” : “2” {
                            “col_3” : “2” {
                                      “col_4” : “1”,
                                      “col_4” : “2”,
                                      “col_4” : “3”  } } }, 
         “col_1” : “2”  {
                  “col_2” : “1” {
                            “col_3” : “1” …

To avoid full scans of the partition and to make queries more efficient, the database requires that the higher level columns in the sort order (col_1, col_2, and col_3) are identified using the equals or IN operators. Ranges are allowed on the last column (col_4).

Selecting data from a clustering segment

For example, to find only values in column 4 that are less than or equal to 2:

SELECT * FROM numbers 
WHERE key = 100 
AND col_1 = 1 AND col_2 = 1 AND col_3 = 1
AND col_4 <= 2;

The results contain the first two rows:

 key | col_1 | col_2 | col_3 | col_4
-----+-------+-------+-------+-------
 100 |     1 |     1 |     1 |     1
 100 |     1 |     1 |     1 |     2

(2 rows)

The IN operator can impact performance on medium-large datasets. When selecting multiple segments, the database loads and filters all the specified segments.

For example, to find all values less than or equal to 2 in both col_1 segments 1 and 2:

SELECT * FROM numbers 
WHERE key = 100 
AND col_1 IN (1, 2) 
AND col_2 = 1 AND col_3 = 1
AND col_4 <= 2;

The following visualization shows all the segments the database must load to filter multiple segments:

The results return the range from both segments.

 key | col_1 | col_2 | col_3 | col_4
-----+-------+-------+-------+-------
 100 |     1 |     1 |     1 |     1
 100 |     1 |     1 |     1 |     2
 100 |     2 |     1 |     1 |     1
 100 |     2 |     1 |     1 |     2

(4 rows)

Tip: Use TRACING to analyze the impact of various queries in your environment.

SELECT * FROM numbers 
WHERE key = 100 
AND col_4 <= 2;

InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "col_4" 
cannot be restricted as preceding column "col_1" is not restricted"

SELECT * FROM numbers 
WHERE key = 100 AND col_1 = 1
AND col_2 > 1;

 key | col_1 | col_2 | col_3 | col_4
-----+-------+-------+-------+-------
 100 |     1 |     2 |     2 |     1
 100 |     1 |     2 |     2 |     2
 100 |     1 |     2 |     2 |     3

(3 rows)

Returning ranges that span clustering segments

Slicing provides a way to look at an entire clustering segment and find a row that matches values in multiple columns. The slice logical statement finds a single row location and allows you to return all the rows before, including, between, or after the row.

(clustering1, clustering2[, …]) range_operator (value1, value2[, …]) 
[AND (clustering1, clustering2[, …]) range_operator (value1, value2[, …])]

Slices across full partition

The slice determines the exact location within the sorted columns; therefore, the highest level is evaluated first, then the second, and so forth in order to drill down to the precise row location. The following statement identifies the row where column 1, 2, and 3 are equal to 2 and column 4 is less than or equal to 1.

SELECT * FROM numbers
WHERE key = 100 
AND (col_1, col_2, col_3, col_4) <= (2, 2, 2, 1);

The database locates the matching row and then returns every record before the identified row in the results set.

Tip: Where col_1 = 1, col_4 contains values 2 and 3 in the results (which are greater than 1). The database is NOT filtering on all values in column 4, it is finding the exact location shown in dark green. Once it locates the row, the evaluation ends.

The location might be hypothetical, that is the dataset does not contain a row that exactly matches the values. For example, the query specifies slice values of (2, 1, 1, 4).

SELECT * FROM numbers
WHERE key = 100 
AND (col_1, col_2, col_3, col_4) <= (2, 1, 1, 4);

The query finds where the row would be in the order if a row with those values existed and returns all rows before it:

Note: The value of column 4 is only evaluated to locate the row placement within the clustering segment. The database locates the segment and then finds col_4 = 4. After finding the location, it returns the row and all the rows before it in the sort order (which in this case spans all clustering columns).

Slices of clustering segments

The same rules apply to slice restrictions when finding a slice on a lower level segment; identify the higher level clustering segments using equals or IN and specify a range on the lower segments.

For example, to return rows where the value is greater than (1, 3) and less than or equal to (2, 5):

SELECT * FROM numbers  
WHERE key = 100 AND col_1 = 1 AND col_2 = 1 
AND (col_3, col_4) >= (1, 2) 
AND (col_3, col_4) < (2, 3);

When finding a between range, the two slice statements must be on the same columns for lowest columns in the hierarchy.

SELECT * FROM numbers
WHERE key = 100 AND col_1 = 1  
AND (col_2, col_3, col_4) >= (1, 1, 2) 
AND (col_3, col_4) < (2, 3);

InvalidRequest: Error from server: code=2200 [Invalid query] 
message="Column "col_3" cannot be restricted by two inequalities not starting with the same column"