[Solved] Query on timeuuid type does not return correct results via cql

EverSQL Database Performance Knowledge Base

Query on timeuuid type does not return correct results via cql

I am attempting to perform a query using timeuuid to retrieve a result set.

Table is as such:

CREATE TABLE mds.arguments_by_id (
  argument_id timeuuid PRIMARY KEY,
  category text,
  title text
)

When I select the dateOf() for all of the data in the table, I get the following:

select dateOf(argument_id),argument_id from arguments_by_id ;

 dateOf(argument_id)      | argument_id
 -------------------------+--------------------------------------
 2014-12-29 13:50:07-0500 | 81f990c0-8f8b-11e4-abb3-5d7a44c0d8a8
 2014-12-29 14:01:43-0500 | 20def1c0-8f8d-11e4-abb3-5d7a44c0d8a8
 2014-12-29 14:01:58-0500 | 29b50f50-8f8d-11e4-abb3-5d7a44c0d8a8
 2014-12-29 14:03:01-0500 | 4f6b72c0-8f8d-11e4-bc90-abc65998337a

(4 rows)

The query I'd like to run needs to return results where the argument_id (date) is greater than a specified date: 

select dateOf(argument_id),argument_id from arguments_by_id where token(argument_id) > token(maxTimeuuid('2014-12-28 15:31:00-0500'));

However that query returns a (seemingly) incomplete result set when compared to the previous select:

 dateOf(argument_id)      | argument_id
--------------------------+--------------------------------------
 2014-12-29 14:01:43-0500 | 20def1c0-8f8d-11e4-abb3-5d7a44c0d8a8
 2014-12-29 14:01:58-0500 | 29b50f50-8f8d-11e4-abb3-5d7a44c0d8a8
 2014-12-29 14:03:01-0500 | 4f6b72c0-8f8d-11e4-bc90-abc65998337a

(3 rows)

My goal was to minimize the number of keys - but am wondering if I am 1) incurring a performance hit by going this route and 2) trying to do too much with the primary key.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 7): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `argument_id` is indexed, the index won’t be used as it’s wrapped with the function `token`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
The optimized query:
SELECT
        dateOf(arguments_by_id.argument_id),
        arguments_by_id.argument_id 
    FROM
        arguments_by_id 
    WHERE
        token(arguments_by_id.argument_id) > token(maxTimeuuid('2014-12-28 15:31:00-0500'))

Related Articles



* original question posted on StackOverflow here.