Indexes in Analytics using N1QL query for Optimize

Analytics
1

Hi,

Need your help, for optimizing Analytics Query.

I have document with > 12 millions records.
I use query:

SELECT COUNT(*) 
FROM `core`.report.transactions_account 
WHERE timestamp BETWEEN STR_TO_MILLIS('2021-12-01T00:00:00Z')/1000 AND STR_TO_MILLIS('2021-12-31T00:00:00Z')/1000

Query Execution Time:

success elapsed: 6.22s execution: 6.21s docs scanned: 12690150 docs returned: 1 size: 19 bytes

After create index for field ‘timestamp’:
CREATE INDEX idx_timestamp ON core.report.transactions_account(timestamp:BIGINT)

And run the same query, and get results:

Screenshot 2022-01-10 112804
Screenshot 2022-01-10 1128041151×149 14.8 KB

Why does it take longer with the index?

2

Hi @Reals ,
For this query Analytics currently fetches documents from the main dataset for each matching document id that it got from the index. Looks like the majority of your documents match the WHERE clause, therefore scanning the main dataset directly could be faster (i.e. when this query runs without the index).

3

That is, in this case, indexing will not help in speeding up the selection?

And why is fetching from the Bucket faster than from Analytics?

4

Yes, if most documents match the WHERE clause then scanning the whole dataset will in general be faster that fetching those documents using an index.