N1QL not responding correctly when an index is created and used!

[andreas_a]

Example Query:

SELECT conv.conv_messages[0:1] AS conv_messages
FROM spots AS conv
WHERE conv.type = ‘conversation’
AND conv.conv_id = ‘2_1571120142’
AND ANY msg IN conv.conv_messages
SATISFIES (ANY msgr IN msg.msg_recipients
SATISFIES (msgr.user_id = 6)
END) END

The correct response of the above should be:

[
{
“conv_messages”: [
{
“msg_datetime_created”: “2019-10-15 06:15:42”,
“msg_id”: “conversation_2_1571120142_2_1571120142”,
“msg_owner_id”: 2,
“msg_recipients”: [
{
“msg_is_read”: false,
“user_id”: 169
},
{
“msg_is_read”: false,
“user_id”: 524
},
{
“msg_is_read”: true,
“user_id”: 322
},
{
“msg_is_read”: false,
“user_id”: 298
},
{
“msg_is_read”: false,
“user_id”: 230
},
{
“msg_is_read”: false,
“user_id”: 758
},
{
“msg_is_read”: true,
“user_id”: 241
},
{
“msg_is_read”: true,
“user_id”: 5
},
{
“msg_is_read”: false,
“user_id”: 6
},
{
“msg_is_read”: false,
“user_id”: 7
},
{
“msg_is_read”: true,
“user_id”: 9
},
{
“msg_is_read”: true,
“user_id”: 11
},
{
“msg_is_read”: false,
“user_id”: 16
},
{
“msg_is_read”: true,
“user_id”: 27
},
{
“msg_is_read”: true,
“user_id”: 29
},
{
“msg_is_read”: true,
“user_id”: 35
},
{
“msg_is_read”: true,
“user_id”: 39
},
{
“msg_is_read”: false,
“user_id”: 49
},
{
“msg_is_read”: false,
“user_id”: 51
},
{
“msg_is_read”: true,
“user_id”: 63
},
{
“msg_is_read”: false,
“user_id”: 64
},
{
“msg_is_read”: true,
“user_id”: 95
},
{
“msg_is_read”: true,
“user_id”: 131
},
{
“msg_is_read”: false,
“user_id”: 235
},
{
“msg_is_read”: true,
“user_id”: 247
},
{
“msg_is_read”: false,
“user_id”: 251
},
{
“msg_is_read”: true,
“user_id”: 254
},
{
“msg_is_read”: true,
“user_id”: 257
},
{
“msg_is_read”: true,
“user_id”: 265
},
{
“msg_is_read”: true,
“user_id”: 271
},
{
“msg_is_read”: true,
“user_id”: 279
},
{
“msg_is_read”: true,
“user_id”: 288
},
{
“msg_is_read”: true,
“user_id”: 290
},
{
“msg_is_read”: true,
“user_id”: 304
},
{
“msg_is_read”: true,
“user_id”: 306
},
{
“msg_is_read”: true,
“user_id”: 314
},
{
“msg_is_read”: true,
“user_id”: 316
},
{
“msg_is_read”: true,
“user_id”: 319
},
{
“msg_is_read”: false,
“user_id”: 320
},
{
“msg_is_read”: true,
“user_id”: 321
},
{
“msg_is_read”: true,
“user_id”: 1948
},
{
“msg_is_read”: true,
“user_id”: 2503
},
{
“msg_is_read”: true,
“user_id”: 2525
},
{
“msg_is_read”: true,
“user_id”: 2537
},
{
“msg_is_read”: true,
“user_id”: 3026
},
{
“msg_is_read”: false,
“user_id”: 3126
},
{
“msg_is_read”: true,
“user_id”: 3201
},
{
“msg_is_read”: true,
“user_id”: 3360
},
{
“msg_is_read”: true,
“user_id”: 3361
},
{
“msg_is_read”: true,
“user_id”: 3362
},
{
“msg_is_read”: true,
“user_id”: 3364
},
{
“msg_is_read”: false,
“user_id”: 3389
},
{
“msg_is_read”: true,
“user_id”: 3834
},
{
“msg_is_read”: true,
“user_id”: 4191
},
{
“msg_is_read”: true,
“user_id”: 6223
},
{
“msg_is_read”: true,
“user_id”: 6224
},
{
“msg_is_read”: true,
“user_id”: 6229
},
{
“msg_is_read”: true,
“user_id”: 6230
},
{
“msg_is_read”: true,
“user_id”: 6231
},
{
“msg_is_read”: true,
“user_id”: 6233
},
{
“msg_is_read”: true,
“user_id”: 6234
},
{
“msg_is_read”: false,
“user_id”: 6235
},
{
“msg_is_read”: true,
“user_id”: 6242
},
{
“msg_is_read”: true,
“user_id”: 6243
},
{
“msg_is_read”: true,
“user_id”: 6245
},
{
“msg_is_read”: true,
“user_id”: 6265
},
{
“msg_is_read”: false,
“user_id”: 6278
},
{
“msg_is_read”: false,
“user_id”: 6279
},
{
“msg_is_read”: false,
“user_id”: 6280
},
{
“msg_is_read”: false,
“user_id”: 6281
},
{
“msg_is_read”: false,
“user_id”: 6285
},
{
“msg_is_read”: true,
“user_id”: 6289
},
{
“msg_is_read”: true,
“user_id”: 6290
},
{
“msg_is_read”: false,
“user_id”: 6291
},
{
“msg_is_read”: false,
“user_id”: 6294
},
{
“msg_is_read”: false,
“user_id”: 6300
},
{
“msg_is_read”: false,
“user_id”: 6309
},
{
“msg_is_read”: false,
“user_id”: 6310
},
{
“msg_is_read”: false,
“user_id”: 6317
},
{
“msg_is_read”: true,
“user_id”: 6359
},
{
“msg_is_read”: true,
“user_id”: 6360
},
{
“msg_is_read”: false,
“user_id”: 6373
},
{
“msg_is_read”: true,
“user_id”: 6402
},
{
“msg_is_read”: true,
“user_id”: 6403
},
{
“msg_is_read”: false,
“user_id”: 6409
},
{
“msg_is_read”: false,
“user_id”: 6416
},
{
“msg_is_read”: false,
“user_id”: 6417
},
{
“msg_is_read”: true,
“user_id”: 6445
},
{
“msg_is_read”: false,
“user_id”: 6588
},
{
“msg_is_read”: false,
“user_id”: 6600
},
{
“msg_is_read”: false,
“user_id”: 6602
},
{
“msg_is_read”: true,
“user_id”: 6605
},
{
“msg_is_read”: false,
“user_id”: 6616
},
{
“msg_is_read”: false,
“user_id”: 6637
},
{
“msg_is_read”: false,
“user_id”: 6649
},
{
“msg_is_read”: false,
“user_id”: 6737
},
{
“msg_is_read”: false,
“user_id”: 6769
},
{
“msg_is_read”: false,
“user_id”: 6785
},
{
“msg_is_read”: false,
“user_id”: 6799
},
{
“msg_is_read”: false,
“user_id”: 6817
},
{
“msg_is_read”: false,
“user_id”: 6820
},
{
“msg_is_read”: false,
“user_id”: 6821
},
{
“msg_is_read”: false,
“user_id”: 6822
},
{
“msg_is_read”: false,
“user_id”: 6851
},
{
“msg_is_read”: false,
“user_id”: 6863
},
{
“msg_is_read”: false,
“user_id”: 6865
},
{
“msg_is_read”: false,
“user_id”: 6869
},
{
“msg_is_read”: false,
“user_id”: 6870
},
{
“msg_is_read”: false,
“user_id”: 6871
},
{
“msg_is_read”: false,
“user_id”: 6880
},
{
“msg_is_read”: false,
“user_id”: 6881
},
{
“msg_is_read”: false,
“user_id”: 6883
},
{
“msg_is_read”: false,
“user_id”: 6884
},
{
“msg_is_read”: false,
“user_id”: 6952
},
{
“msg_is_read”: false,
“user_id”: 6954
},
{
“msg_is_read”: false,
“user_id”: 6966
},
{
“msg_is_read”: false,
“user_id”: 6967
},
{
“msg_is_read”: false,
“user_id”: 6968
},
{
“msg_is_read”: false,
“user_id”: 6969
},
{
“msg_is_read”: false,
“user_id”: 6992
},
{
“msg_is_read”: false,
“user_id”: 6999
},
{
“msg_is_read”: false,
“user_id”: 7000
},
{
“msg_is_read”: false,
“user_id”: 7001
},
{
“msg_is_read”: false,
“user_id”: 7002
},
{
“msg_is_read”: false,
“user_id”: 7003
},
{
“msg_is_read”: false,
“user_id”: 7005
},
{
“msg_is_read”: false,
“user_id”: 7006
},
{
“msg_is_read”: false,
“user_id”: 7052
},
{
“msg_is_read”: false,
“user_id”: 7053
},
{
“msg_is_read”: false,
“user_id”: 7054
},
{
“msg_is_read”: false,
“user_id”: 7056
},
{
“msg_is_read”: false,
“user_id”: 7058
},
{
“msg_is_read”: false,
“user_id”: 7059
},
{
“msg_is_read”: false,
“user_id”: 7060
},
{
“msg_is_read”: false,
“user_id”: 7061
},
{
“msg_is_read”: false,
“user_id”: 7077
},
{
“msg_is_read”: false,
“user_id”: 7096
},
{
“msg_is_read”: false,
“user_id”: 7099
},
{
“msg_is_read”: false,
“user_id”: 7100
},
{
“msg_is_read”: false,
“user_id”: 7101
},
{
“msg_is_read”: false,
“user_id”: 7104
},
{
“msg_is_read”: false,
“user_id”: 7107
},
{
“msg_is_read”: false,
“user_id”: 7109
},
{
“msg_is_read”: false,
“user_id”: 7110
},
{
“msg_is_read”: false,
“user_id”: 7111
},
{
“msg_is_read”: false,
“user_id”: 7113
},
{
“msg_is_read”: false,
“user_id”: 7114
},
{
“msg_is_read”: false,
“user_id”: 7115
},
{
“msg_is_read”: false,
“user_id”: 7116
},
{
“msg_is_read”: false,
“user_id”: 7117
},
{
“msg_is_read”: true,
“user_id”: 7118
},
{
“msg_is_read”: true,
“user_id”: 7119
},
{
“msg_is_read”: false,
“user_id”: 7134
},
{
“msg_is_read”: false,
“user_id”: 7135
},
{
“msg_is_read”: false,
“user_id”: 7146
},
{
“msg_is_read”: false,
“user_id”: 7147
},
{
“msg_is_read”: false,
“user_id”: 7148
},
{
“msg_is_read”: false,
“user_id”: 7149
},
{
“msg_is_read”: false,
“user_id”: 7164
},
{
“msg_is_read”: true,
“user_id”: 7166
},
{
“msg_is_read”: false,
“user_id”: 7181
},
{
“msg_is_read”: false,
“user_id”: 7183
},
{
“msg_is_read”: true,
“user_id”: 7184
},
{
“msg_is_read”: false,
“user_id”: 7185
},
{
“msg_is_read”: false,
“user_id”: 7186
},
{
“msg_is_read”: true,
“user_id”: 7187
},
{
“msg_is_read”: false,
“user_id”: 7188
},
{
“msg_is_read”: false,
“user_id”: 7191
},
{
“msg_is_read”: false,
“user_id”: 7197
},
{
“msg_is_read”: false,
“user_id”: 7198
},
{
“msg_is_read”: false,
“user_id”: 7200
},
{
“msg_is_read”: false,
“user_id”: 7201
},
{
“msg_is_read”: false,
“user_id”: 7203
},
{
“msg_is_read”: false,
“user_id”: 7224
},
{
“msg_is_read”: false,
“user_id”: 7229
},
{
“msg_is_read”: false,
“user_id”: 7230
},
{
“msg_is_read”: false,
“user_id”: 7251
},
{
“msg_is_read”: false,
“user_id”: 7289
},
{
“msg_is_read”: false,
“user_id”: 7307
},
{
“msg_is_read”: false,
“user_id”: 7308
},
{
“msg_is_read”: false,
“user_id”: 7309
},
{
“msg_is_read”: false,
“user_id”: 7310
},
{
“msg_is_read”: false,
“user_id”: 7326
},
{
“msg_is_read”: false,
“user_id”: 7398
},
{
“msg_is_read”: false,
“user_id”: 7410
},
{
“msg_is_read”: false,
“user_id”: 7439
}
],
“msg_text”: “AT LEAST 1170”
}
]
}
]

When the following INDEX is created:

CREATE INDEX idx_conversations_of_user_inbox
ON spots
((distinct (array (
distinct (array (
msgr.user_id
) for msgr in (msg.msg_recipients) end)
) for msg in conv_messages end)))
WHERE (type = “conversation”)

The query does respond with empty result-set!!!

This is the EXPLAIN output:

{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “DistinctScan”,
“scan”: {
“#operator”: “IndexScan3”,
“as”: “conv”,
“index”: “idx_conversations_of_user_inbox”,
“index_id”: “36dc9bd5aa5bf049”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “spots”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“high”: “6”,
“inclusion”: 3,
“low”: “6”
}
]
}
],
“using”: “gsi”
}
},
{
“#operator”: “Fetch”,
“as”: “conv”,
“keyspace”: “spots”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((conv.type) = “conversation”) and ((conv.conv_id) = “2_1571120142”)) and any msg in (conv.conv_messages) satisfies any msgr in (msg.msg_recipients) satisfies ((msgr.user_id) = 6) end end)”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “conv_messages”,
“expr”: “((conv.conv_messages)[0 : 1])”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT conv.conv_messages[0:1] AS conv_messages \nFROM spots AS conv \nWHERE conv.type = ‘conversation’ \nAND conv.conv_id = ‘2_1571120142’\nAND ANY msg IN conv.conv_messages \n SATISFIES (ANY msgr IN msg.msg_recipients \n SATISFIES (msgr.user_id = 6)\n END) END”
}

[vsr1]

Plan looks fine. Check out the indexer has entries. Also indexer.log see if any documents skipped indexing (depends on CB version). Also checkout profile and see ItemsIn/ItemsOut from IndexScan3?

[andreas_a]

We are on “Couchbase 6.0 Community Edition build 1693”.
Can you please explain how to check if this specific document is skipped (or point me to related documentation article - it was not possible to find ones myself).

If this is the case and documents are in fact skipped, what are the possible resolutions?

[vsr1]

Open indexer.log and check any skipped document.
Identify the document key that suppose to give and try USE KEYS (avoid index scan) and see if you get output.

Did document you expecting has these fields

{"type" : "conversation", 
"conv_id" : "2_1571120142"}

[andreas_a]

Indeed indexer.log does skip some documents!

Indexer.log (while creating the index)

2019-10-25T10:44:28.821+00:00 [Error] ForestDBSlice::insert SliceId 0 IndexInstId 9801964201063376551 Error in creating compostite new secondary keys. Skipping docid:<ud>(conversation_63_1571211589)</ud> Error: Array to be indexed too long

The document reported as skipped in the logs is indeed not fetched by the related query that uses the index.
It includes an array of 148 items (which is relatively small for our business case).

What are the possible resolutions for this issue?
Obviously it is not an option for us not to use an index to match the queries on these documents.

Document skipped:

  {
    "conv_messages": [
      {
        "msg_datetime_created": "2019-10-16 07:39:49",
        "msg_id": "conversation_63_1571211589_63_1571211589",
        "msg_owner_id": 63,
        "msg_recipients": [
          {
            "msg_is_read": true,
            "user_id": 39
          },
          {
            "msg_is_read": false,
            "user_id": 231
          },
          {
            "msg_is_read": true,
            "user_id": 2
          },
          {
            "msg_is_read": true,
            "user_id": 11
          },
          {
            "msg_is_read": false,
            "user_id": 24
          },
          {
            "msg_is_read": true,
            "user_id": 27
          },
          {
            "msg_is_read": false,
            "user_id": 64
          },
          {
            "msg_is_read": true,
            "user_id": 65
          },
          {
            "msg_is_read": false,
            "user_id": 95
          },
          {
            "msg_is_read": true,
            "user_id": 131
          },
          {
            "msg_is_read": false,
            "user_id": 233
          },
          {
            "msg_is_read": true,
            "user_id": 306
          },
          {
            "msg_is_read": true,
            "user_id": 320
          },
          {
            "msg_is_read": false,
            "user_id": 819
          },
          {
            "msg_is_read": true,
            "user_id": 2503
          },
          {
            "msg_is_read": true,
            "user_id": 2525
          },
          {
            "msg_is_read": true,
            "user_id": 3364
          },
          {
            "msg_is_read": false,
            "user_id": 3389
          },
          {
            "msg_is_read": false,
            "user_id": 3834
          },
          {
            "msg_is_read": false,
            "user_id": 6223
          },
          {
            "msg_is_read": false,
            "user_id": 6224
          },
          {
            "msg_is_read": false,
            "user_id": 6225
          },
          {
            "msg_is_read": false,
            "user_id": 6242
          },
          {
            "msg_is_read": false,
            "user_id": 6243
          },
          {
            "msg_is_read": false,
            "user_id": 6245
          },
          {
            "msg_is_read": false,
            "user_id": 6265
          },
          {
            "msg_is_read": false,
            "user_id": 6278
          },
          {
            "msg_is_read": false,
            "user_id": 6279
          },
          {
            "msg_is_read": false,
            "user_id": 6280
          },
          {
            "msg_is_read": false,
            "user_id": 6281
          },
          {
            "msg_is_read": false,
            "user_id": 6285
          },
          {
            "msg_is_read": true,
            "user_id": 6289
          },
          {
            "msg_is_read": false,
            "user_id": 6290
          },
          {
            "msg_is_read": false,
            "user_id": 6291
          },
          {
            "msg_is_read": false,
            "user_id": 6294
          },
          {
            "msg_is_read": false,
            "user_id": 6300
          },
          {
            "msg_is_read": false,
            "user_id": 6309
          },
          {
            "msg_is_read": false,
            "user_id": 6310
          },
          {
            "msg_is_read": false,
            "user_id": 6317
          },
          {
            "msg_is_read": false,
            "user_id": 6359
          },
          {
            "msg_is_read": false,
            "user_id": 6360
          },
          {
            "msg_is_read": false,
            "user_id": 6373
          },
          {
            "msg_is_read": false,
            "user_id": 6402
          },
          {
            "msg_is_read": false,
            "user_id": 6403
          },
          {
            "msg_is_read": false,
            "user_id": 6409
          },
          {
            "msg_is_read": false,
            "user_id": 6416
          },
          {
            "msg_is_read": false,
            "user_id": 6417
          },
          {
            "msg_is_read": true,
            "user_id": 6445
          },
          {
            "msg_is_read": false,
            "user_id": 6588
          },
          {
            "msg_is_read": false,
            "user_id": 6600
          },
          {
            "msg_is_read": false,
            "user_id": 6602
          },
          {
            "msg_is_read": true,
            "user_id": 6605
          },
          {
            "msg_is_read": false,
            "user_id": 6616
          },
          {
            "msg_is_read": false,
            "user_id": 6637
          },
          {
            "msg_is_read": false,
            "user_id": 6649
          },
          {
            "msg_is_read": false,
            "user_id": 6737
          },
          {
            "msg_is_read": false,
            "user_id": 6769
          },
          {
            "msg_is_read": false,
            "user_id": 6785
          },
          {
            "msg_is_read": false,
            "user_id": 6799
          },
          {
            "msg_is_read": false,
            "user_id": 6817
          },
          {
            "msg_is_read": false,
            "user_id": 6820
          },
          {
            "msg_is_read": false,
            "user_id": 6821
          },
          {
            "msg_is_read": false,
            "user_id": 6822
          },
          {
            "msg_is_read": false,
            "user_id": 6851
          },
          {
            "msg_is_read": false,
            "user_id": 6863
          },
          {
            "msg_is_read": false,
            "user_id": 6865
          },
          {
            "msg_is_read": false,
            "user_id": 6869
          },
          {
            "msg_is_read": false,
            "user_id": 6870
          },
          {
            "msg_is_read": false,
            "user_id": 6871
          },
          {
            "msg_is_read": false,
            "user_id": 6880
          },
          {
            "msg_is_read": false,
            "user_id": 6881
          },
          {
            "msg_is_read": false,
            "user_id": 6883
          },
          {
            "msg_is_read": false,
            "user_id": 6884
          },
          {
            "msg_is_read": false,
            "user_id": 6952
          },
          {
            "msg_is_read": false,
            "user_id": 6954
          },
          {
            "msg_is_read": false,
            "user_id": 6966
          },
          {
            "msg_is_read": false,
            "user_id": 6967
          },
          {
            "msg_is_read": false,
            "user_id": 6968
          },
          {
            "msg_is_read": false,
            "user_id": 6969
          },
          {
            "msg_is_read": false,
            "user_id": 6992
          },
          {
            "msg_is_read": false,
            "user_id": 6999
          },
          {
            "msg_is_read": false,
            "user_id": 7000
          },
          {
            "msg_is_read": false,
            "user_id": 7001
          },
          {
            "msg_is_read": false,
            "user_id": 7002
          },
          {
            "msg_is_read": false,
            "user_id": 7003
          },
          {
            "msg_is_read": false,
            "user_id": 7005
          },
          {
            "msg_is_read": false,
            "user_id": 7006
          },
          {
            "msg_is_read": false,
            "user_id": 7052
          },
          {
            "msg_is_read": false,
            "user_id": 7053
          },
          {
            "msg_is_read": false,
            "user_id": 7054
          },
          {
            "msg_is_read": false,
            "user_id": 7056
          },
          {
            "msg_is_read": false,
            "user_id": 7058
          },
          {
            "msg_is_read": false,
            "user_id": 7059
          },
          {
            "msg_is_read": false,
            "user_id": 7060
          },
          {
            "msg_is_read": false,
            "user_id": 7061
          },
          {
            "msg_is_read": false,
            "user_id": 7077
          },
          {
            "msg_is_read": false,
            "user_id": 7096
          },
          {
            "msg_is_read": false,
            "user_id": 7099
          },
          {
            "msg_is_read": false,
            "user_id": 7100
          },
          {
            "msg_is_read": false,
            "user_id": 7101
          },
          {
            "msg_is_read": false,
            "user_id": 7104
          },
          {
            "msg_is_read": false,
            "user_id": 7107
          },
          {
            "msg_is_read": false,
            "user_id": 7109
          },
          {
            "msg_is_read": false,
            "user_id": 7110
          },
          {
            "msg_is_read": false,
            "user_id": 7111
          },
          {
            "msg_is_read": false,
            "user_id": 7113
          },
          {
            "msg_is_read": false,
            "user_id": 7114
          },
          {
            "msg_is_read": false,
            "user_id": 7115
          },
          {
            "msg_is_read": false,
            "user_id": 7116
          },
          {
            "msg_is_read": false,
            "user_id": 7117
          },
          {
            "msg_is_read": false,
            "user_id": 7118
          },
          {
            "msg_is_read": false,
            "user_id": 7119
          },
          {
            "msg_is_read": false,
            "user_id": 7134
          },
          {
            "msg_is_read": false,
            "user_id": 7135
          },
          {
            "msg_is_read": false,
            "user_id": 7146
          },
          {
            "msg_is_read": false,
            "user_id": 7147
          },
          {
            "msg_is_read": false,
            "user_id": 7148
          },
          {
            "msg_is_read": false,
            "user_id": 7149
          },
          {
            "msg_is_read": false,
            "user_id": 7164
          },
          {
            "msg_is_read": false,
            "user_id": 7166
          },
          {
            "msg_is_read": false,
            "user_id": 7181
          },
          {
            "msg_is_read": false,
            "user_id": 7183
          },
          {
            "msg_is_read": false,
            "user_id": 7184
          },
          {
            "msg_is_read": false,
            "user_id": 7185
          },
          {
            "msg_is_read": false,
            "user_id": 7186
          },
          {
            "msg_is_read": false,
            "user_id": 7187
          },
          {
            "msg_is_read": false,
            "user_id": 7188
          },
          {
            "msg_is_read": false,
            "user_id": 7191
          },
          {
            "msg_is_read": false,
            "user_id": 7197
          },
          {
            "msg_is_read": false,
            "user_id": 7198
          },
          {
            "msg_is_read": false,
            "user_id": 7200
          },
          {
            "msg_is_read": false,
            "user_id": 7201
          },
          {
            "msg_is_read": false,
            "user_id": 7203
          },
          {
            "msg_is_read": false,
            "user_id": 7224
          },
          {
            "msg_is_read": false,
            "user_id": 7229
          },
          {
            "msg_is_read": false,
            "user_id": 7230
          },
          {
            "msg_is_read": false,
            "user_id": 7251
          },
          {
            "msg_is_read": false,
            "user_id": 7289
          },
          {
            "msg_is_read": false,
            "user_id": 7307
          },
          {
            "msg_is_read": false,
            "user_id": 7308
          },
          {
            "msg_is_read": false,
            "user_id": 7309
          },
          {
            "msg_is_read": false,
            "user_id": 7310
          },
          {
            "msg_is_read": false,
            "user_id": 7326
          },
          {
            "msg_is_read": false,
            "user_id": 7398
          },
          {
            "msg_is_read": false,
            "user_id": 7410
          },
          {
            "msg_is_read": false,
            "user_id": 7439
          },
          {
            "msg_is_read": false,
            "user_id": 3201
          },
          {
            "msg_is_read": false,
            "user_id": 3361
          }
        ],
        "msg_text": "                To all friends, followers and spotters"
      }
    ]
  }
]```

[vsr1]

try use the following index and if required increase index key size suggested in the posts

CREATE INDEX idx_conversations_of_user_inbox ON spots
(conv_id, (distinct (array (distinct (array ( msgr . user_id) for msgr in ( msg . msg_recipients ) end)) for msg in conv_messages end)))
WHERE ( type = “conversation”)

How to read max_array_seckey_size setting (Version: 4.5.1-2844 Community Edition (build-2844))

The endpoint referenced here: https://developer.couchbase.com/documentation/server/current/rest-api/get-settings-indexes.html doesn’t give us the granularity we need. It is: GET http://<host>:8091/settings/indexes I also know how to set the value in bytes: curl -X POST -u admin:couchbase http://localhost:9102/settings --data '{"indexer.settings.max_array_seckey_size": X}' We are trying to reproduce this issue which we believe happened in our deployment https://issues.couchbase.com/browse/MB…

Some documents are not indexed

Hi, I have about 100~120 docs with the following data format { "id": 1, "form": "product", "meta": { } "values": { "field1": "value", "field2": "value" } } I created an index to cover id, meta, and values. Covering index create index `test_cover` on bucket(id, meta, `values`) where form="product" Regular index create index `test_form` on bucket(form) where form="product" I’m seeing unexpected behavior with covering index. The following query…

cc @deepkaran.salooja

[andreas_a]

The original value of the setting was the following:
"indexer.settings.max_array_seckey_size":10240

After increasing it x10 (102400 bytes) no documents appear to have been skipped.

However, we need more info to decide whether the setting change is appropriate:

1. Based on the document structure defined above, documents of how many array items should we expect that will have no issues?
2. How significantly will this setting affect overall performance of CB indexer?
3. The size taken into consideration for max_array_seckey_size what does it depend on? For example do JSON keys sizes also play any role? Should we look into trying to limit this size as much as possible too?
4. If requirements of our particular implementation impose us to support ~1000 messages for ~1000 recipients, will we be better of if we switch to a “referenced” design approach for the sub-documents instead of an “embedded” one?

Can you point us to any documentation about this setting where we could study in more detail how we should approach this issue?

[vsr1]

Check out formula : Encoded array key too long (> 30978). Skipped - #35 by krishan.jangid
Index key size can impact indexer performance. cc @deepkaran.salooja .
If the query is not covered it can impact query performance/cluster scalability too. Example: If you have ~1000 messages for ~1000 recipients, i.e. gives big document in KBs also ANY clause is looping construct i.e loop of n*n (i.e 1Million loops) If the value looking at the front it is good if at the end Filter evaluation needs to go through loops to validate and it can take time.

Arrays can work small size if bigger and nested may be join might be better.