Using GSI to improve query with time value

SQL++
1

Hello,
I am having a N1QL query as follow to get the time the message was created, and the time that a certain user read the message:

SELECT createdAt, readTime.`someuserID` AS readTime
FROM bucketname
WHERE docType = "message"
AND readTime.`someuserID` IS NOT MISSING

The message document looks something like this:

msgID: {
    "docType": "message",
    "createdAt" : "2019-03-13T23:20:11.128548Z" (I just upsert a time.Time value in Golang to couchbase)
    "text": "someText"
    "readTime": {
        "someUserID1": "2019-05-31T15:53:29.939124813-07:00"
        "someUserID2": "2019-05-29T16:49:50.454339107-07:00"
    }
}

This query at the moment took me more than 1 second to execute, which I would want to improve, but I do not know how I can use GSI to improve it. Thank you in advance

2

GSI Index: CREATE INDEX ix_docType ON bucketname (docType);
You could also consider creating a covering index, i.e. adding all the projected fields to the index. But that would require additional index storage. Note that index will help if your query predicate(s) are highly selective.

3

Thank you a lot for your response. I’m sorry I did not reply sooner.
So I have already had an index on docType before (which I should have mentioned, but I forgot), but since we have about 18000 documents with docType message, it still takes >1s to execute the query (at least from what I understand of index). I now have an index:

CREATE INDEX ix_readTime ON bucketname(readTime) WHERE docType = “message”

and the query changed a bit into:

SELECT createdAt, readTime.`someuserID` AS readTime
FROM bucketname
WHERE docType = "message"
AND readTime IS NOT MISSING
AND readTime.`someuserID` IS NOT MISSING

and the execution time comes down to about 200-300ms. However, from what I understand of GSI, if numerous messages have ‘readTime’ field as not missing (say all 18000 messages for example), then the execution time would increase again. Is there a better way to improve its performance? Thank you once again.

4

If your query returns <300ms with the predicate “readTime IS NOT MISSING”, then the query’s predicate is selective enough to benefit from the index you have. However, if all your docs have the “readTime” valued, then the query will no longer be selective, i.e. the query can return all the 18k docs, and index will not improve the query performance.

5

So is there any other way to check if readTime.someuserID is not missing that can be implemented as a GSI?
Theoretically, if it is possible, I think that it will increase the performance of the query since it only checks for messages that the user has read (readTime.someuserID is not missing) right?

6

You could try: CREATE INDEX idx1 on bucketname(readTime.someuserID, createdAt) where docType = “message”

7

That index would mean that I would have to have an index for each userID in order for the query to perform well right?
I did change the index and query a bit and it seems to be working pretty well now, taking less than 100ms to execute.
My index and query:

CREATE INDEX idx_msg_obj_rt  ON bk1((ALL (ARRAY (m.name) FOR m IN object_pairs(readTime) END))) WHERE (docType = "message")

SELECT createdAt, readTime.`someuserID` as readTime
FROM bucketname
WHERE docType = 'message'
AND readTime IS NOT MISSING
AND ANY m IN OBJECT_PAIRS(readTime) SATISFIES m.name = 'someuserID' END

If I understand this query correctly, then it should be faster even if every ‘message’ docType document has readTime field right?