Using synchronous SOQL performed via Apex or the Salesforce REST API, you can only filter records based on the index fields defined when you created the big object. In other words, it is not possible to filter by the Time__c, Position__c, or Milliseconds__c fields. Records are returned in the order defined by the index fields, defining an ORDER BY clause that does not match the index fields sequence and sorting is not supported.

The order in which you defined the index fields is very important as it determines the constraints of your filter criteria. It will help in the following examples to think about the index fields as being defined from left to right, where the left-most field is RaceId__c, and the right-most field is Lap__c. The following screenshot shows how the fields are defined:

For ease, try out the following examples using the SFDX CLI to perform the query from the command line, though these could equally be performed from Apex code as well. This first example shows the full command line, a query, and a sample of the output:

sfdx force:data:soql:query -q "select RaceId__c, DriverId__c, Lap__c, Time__c from RaceLapHistory__b where RaceId__c = '904'"
...
904 828 7 1:35.692
904 828 8 1:35.768
904 828 9 1:35.603
Total number of records retrieved: 1361.

You can add further index fields to the where clause to further reduce the result set returned. The next example focuses on lap data for race 904 (Spain) as before, but this time only for driver 1 (Lewis Hamilton). Remember to keep in mind the index field order defined on the object when reviewing the following examples; it was from left to right defined as follows, RaceId_c , DriverId__c, and then Lap__c. The following is a valid query:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b 
  where RaceId__c = '904' and DriverId__c = '1'

This next example shows an invalid query:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b 
  where RaceId__c = '904' and Lap__c ='20'"

The preceding query gives the following error:

ERROR: Filters may not have any gaps within the composite key

This is because the record data is stored physically on disc based on all the index field values and their sequence. Thus, it is not possible to perform a query that effectively omits (or skips) the DriverId__c index field value, since without this information, the database simply does not know where to go on the disk to start reading the record data from.

The previous examples are illustrating a specific syntax rule, which states you can only define your filter criteria from left to right and that you may only omit (or skip) index fields to the far right. This is why the preceding first and second examples were valid, as they used the first and then the first and second index fields.

Now that we understand a key rule with respect to specifying filter criteria using the left-most index fields, let's consider how other range operators can be used. This rule states that range operators such as <, >, <=, >=, or IN can only be used on the last right-most index in the criteria. The following is a valid query example:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b 
  where RaceId__c = '904' and DriverId__c = '1' and Lap__c >='20'

The following is an invalid query example:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b 
  where RaceId__c = '904' and DriverId__c > '1' and Lap__c >='20'"

The preceding query gives the following error:

ERROR: Range filter columns must be last in filter in relation to the composite key columns

The following example shows invalid queries since you cannot include any non-index fields in your criteria nor use any aggregate SOQL functions such as SUM or AVG:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b 
  where RaceId__c = '904' and DriverId__c = '1' 
    and Milliseconds__c < 20000"

The preceding query gives the following error:

ERROR: field 'Milliseconds__c' can not be filtered in a query call

The following is another example of an invalid query:

select Count(RaceId__c) 
  from RaceLapHistory__b 
  where RaceId__c = '904'

The preceding query gives the following error:

ERROR: Count function is not supported

To perform aggregate- or non-aggregate-based queries based on non-index fields, you need to move to asynchronous SOQL, which is capable of performing the compute required to navigate larger volumes of records and filter them in memory.

Finally, it is possible via the Salesforce REST API to run an unbounded query (one without the where clause) in order to download all the records in the object:

select RaceId__c, DriverId__c, Lap__c, Time__c 
  from RaceLapHistory__b