Optimizing query that compares dates

SQL++
1

I’m using Couchbase 4.5.1-2845 Community Edition (build-2845) + Sync Gateway. I’ve to query a bucket containing about 100k docs to find documents deleted from CBL before a certain date. This is the query I run:

select meta().id
from H2H_APP_CLIENTI
where _deleted = true 
and _sync.time_saved  <  '2019-02-16T00:00:00'

The date _sync.time_saved is compared against may change every time I run the query.

Running the query without any specific index returns data (18k doc ids) in about 10", because a full primary index scan is performed and data fetch is needed to check the date.

Adding a simple index on _deleted property improves performance and query execution time is 1.5", the new index is scanned and data is fetched to check the date.

With a better (partial) index like this
CREATE INDEX `del_date_idx` ON `H2H_APP_CLIENTI`(_sync.time_saved) WHERE _deleted = true
the execution time is dropped to 450 ms. This index should cover about 27k docs

Same performance using numeric dates:

CREATE INDEX `del_date_idx` ON `H2H_APP_CLIENTI`(STR_TO_MILLIS(_sync.time_saved)) WHERE _deleted = true
select meta().id
from H2H_APP_CLIENTI
where _deleted = true 
and STR_TO_MILLIS(_sync.time_saved) < 1550271600000

My question is the following: is there a way to improve even more the query performance?

2

If you want use numeric dates use this index
CREATE INDEX del_date_idx ON H2H_APP_CLIENTI(STR_TO_MILLIS(_sync.time_saved)) WHERE _deleted = true

3

@vsr1 you are right, is the index I created to use numeric values, I made an error while writing my post, now fixed. Performance is the same with strings or numbers.

4

Nothing can be improved further

1 Like