I have a document which has an Effective Timestamp field which is a String, formatted as a GMT timestamp. The bucket is expected to have millions of records. I have a covering index on the effective timestamp .
The documents in the bucket have a very long retention period of around a year. However there is also a use case to query only last 7 day data. Is is possible to create another index based on effective timestamp between current date and last 7 days? If so, will this help improve the query performance given that I already have this field as part of my covering index?
A sample select query is as below -
select * from testbucket where docType=‘transactiondoc’ and effTS between date_add_str(now_tz(‘UTC’), -7, ‘day’) and now_tz(‘UTC’)
I am trying to create an index as below, but its giving me expression not indexable. What would be the correct way to create such an index?
create index idx_1 on testbucket(
field3) where effTS between date_add_str(now_tz(‘UTC’), -7, ‘day’) and now_tz(‘UTC’)