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'
_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?