Array Index Question

atom_yang · May 21, 2016, 9:16am

I am using Couchbase Server Version: 4.5.0-2203 Enterprise Edition (build-2203) (4.5Beta)
any my sample document looks like the following:

{"id":"R::1","type":"relationship","owner_members":["user1"],"normal_members":["user2","user3"],"guest_members":["user4"]}
{"id":"R::2","type":"relationship","owner_members":["user1","user2"],"normal_members":["user4"],"guest_members":[]}
{"id":"R::3","type":"relationship","owner_members":["user4"],"normal_members":["user1"],"guest_members":["user3"]}

I want to get those documents where user belongs to any members of owner_members/normal_members/guest_members
for example, for user2 as query condition, I can get R::1 and R::2 documents info.
So I want to try to use array index here, I create the following index:

CREATE PRIMARY INDEX `pidx-mydb` ON `mydb` USING GSI;
CREATE INDEX `idx-type` ON `mydb`(type) USING GSI;
CREATE INDEX `idx-rel_owner_members` ON `mydb`(DISTINCT ARRAY o FOR o IN owner_members END) WHERE (type = "relationship");
CREATE INDEX `idx-rel_normal_members` ON `mydb`(DISTINCT ARRAY n FOR n IN normal_members END) WHERE (type = "relationship");
CREATE INDEX `idx-rel_guest_members` ON `mydb`(DISTINCT ARRAY g FOR g IN guest_members END) WHERE (type = "relationship");

My N1QL looks like this:

select meta(a).id,a.owner_members,a.normal_members,a.guest_members
from mydb as a 
where 
(a.type == "relationship" AND ANY AND EVERY o IN a.owner_members SATISFIES o == "user2" END)
OR
(a.type == "relationship" AND ANY AND EVERY n IN a.normal_members SATISFIES n == "user2" END)
OR
(a.type == "relationship" AND ANY AND EVERY g IN a.guest_members SATISFIES g == "user2" END)
;

but EXPLAIN shows only idx-type be used. Following is the result of EXPLAIN:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "DistinctScan",
          "scan": {
            "#operator": "IndexScan",
            "index": "idx-type",
            "index_id": "42c69e2edecf0e60",
            "keyspace": "mydb",
            "namespace": "default",
            "spans": [
              {
                "Range": {
                  "High": [
                    "\"relationship\""
                  ],
                  "Inclusion": 12,
                  "Low": [
                    "\"relationship\""
                  ]
                }
              },
              {
                "Range": {
                  "High": [
                    "\"relationship\""
                  ],
                  "Inclusion": 12,
                  "Low": [
                    "\"relationship\""
                  ]
                }
              },
              {
                "Range": {
                  "High": [
                    "\"relationship\""
                  ],
                  "Inclusion": 12,
                  "Low": [
                    "\"relationship\""
                  ]
                }
              }
            ],
            "using": "gsi"
          }
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Fetch",
                "as": "a",
                "keyspace": "mydb",
                "namespace": "default"
              },
              {
                "#operator": "Filter",
                "condition": "(((((`a`.`type`) = \"relationship\") and any and every `o` in (`a`.`owner_members`) satisfies (`o` = \"user2\") end) or (((`a`.`type`) = \"relationship\") and any and every `n` in (`a`.`normal_members`) satisfies (`n` = \"user2\") end)) or (((`a`.`type`) = \"relationship\") and any and every `g` in (`a`.`guest_members`) satisfies (`g` = \"user2\") end))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "(meta(`a`).`id`)"
                  },
                  {
                    "expr": "(`a`.`owner_members`)"
                  },
                  {
                    "expr": "(`a`.`normal_members`)"
                  },
                  {
                    "expr": "(`a`.`guest_members`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select meta(a).id,a.owner_members,a.normal_members,a.guest_members\nfrom mydb as a \nwhere \n(a.type == \"relationship\" AND ANY AND EVERY o IN a.owner_members SATISFIES o == \"user2\" END)\nOR\n(a.type == \"relationship\" AND ANY AND EVERY n IN a.normal_members SATISFIES n == \"user2\" END)\nOR\n(a.type == \"relationship\" AND ANY AND EVERY g IN a.guest_members SATISFIES g == \"user2\" END)\n;"
  }
]

Is there any way to improve performance by using array index like this?

geraldss · May 21, 2016, 3:46pm

Hi @atom_yang,

CREATE INDEX `idx-rel_members` ON `mydb`(DISTINCT ARRAY m FOR m IN ARRAY_CONCAT(owner_members, ARRAY_CONCAT(normal_members, guest_members)) END) WHERE type = "relationship";

EXPLAIN SELECT META(a).id,a.owner_members,a.normal_members,a.guest_members
FROM mydb AS a 
WHERE a.type == "relationship" AND ANY m IN ARRAY_CONCAT(a.owner_members, ARRAY_CONCAT(normal_members, guest_members)) SATISFIES m == "user2" END;

atom_yang · May 21, 2016, 3:58pm

Thank you very much.It works! Array Index is powerful.

david_segleau · May 23, 2016, 6:54pm

@atom_yang, can you share how you are using Array Indexing in your application? You can respond here or send email to david.segleau at couchbase dot com. Thanks!

atom_yang · May 24, 2016, 3:02am

Sure.
My application is IM like application.User belong to some groups with role(owner/normal/guest).So my documents design for group documents looks like this:

document_key :  GRP::<UUID>
type :  GRP
owner_members : array of userID lists
normal_members : array of userID lists
guest_members : array of userID lists
status : group's status(active/inactive)
name : group's nike name

When user login in my application, In the list at the left will show all groups which user belong to with role. My application can decide what user can do for this group,such as management group for owner user, talk to other users in group for normal user,view group’s profile for guest user.
So I need get groups list with role by user belong any members of owner_members/normal_members/guest_members.
So I create a array index to improve my query efficiency.
In CB 4.1.0 with out Array Index, Execution time is 42.35ms within 1000 documents total;
In CB 4.5 Beta with Array Index and MOI, Execution time is 11.48ms within 1000 documents total;

atom_yang · May 24, 2016, 10:28am

@geraldss what if I want to get the following data:

[
{"user_id":"user1","rel_id":"R::1"},
{"user_id":"user1","rel_id":"R::2"},
{"user_id":"user1","rel_id":"R::3"},
{"user_id":"user2","rel_id":"R::1"},
{"user_id":"user2","rel_id":"R::2"},
{"user_id":"user3","rel_id":"R::1"},
{"user_id":"user3","rel_id":"R::3"},
{"user_id":"user4","rel_id":"R::1"},
{"user_id":"user4","rel_id":"R::2"},
{"user_id":"user4","rel_id":"R::3"}
]

and

{"user_id":"user1","rel_ids":["R::1","R::2","R::3"]},
{"user_id":"user2","rel_ids":["R::1","R::2"]},
{"user_id":"user3","rel_id":["R::1","R::3"]},
{"user_id":"user4","rel_ids":["R::1","R::2","R::3"]}

can I reused Array Index?

geraldss · May 24, 2016, 2:05pm

Hi @atom_yang,

Yes with 4.5 GA.

atom_yang · May 24, 2016, 4:23pm

How should I write N1QL to get the data like following:

[
{"user_id":"user1","rel_id":"R::1"},
{"user_id":"user1","rel_id":"R::2"},
{"user_id":"user1","rel_id":"R::3"},
{"user_id":"user2","rel_id":"R::1"},
{"user_id":"user2","rel_id":"R::2"},
{"user_id":"user3","rel_id":"R::1"},
{"user_id":"user3","rel_id":"R::3"},
{"user_id":"user4","rel_id":"R::1"},
{"user_id":"user4","rel_id":"R::2"},
{"user_id":"user4","rel_id":"R::3"}
]

and

[
{"user_id":"user1","rel_ids":["R::1","R::2","R::3"]},
{"user_id":"user2","rel_ids":["R::1","R::2"]},
{"user_id":"user3","rel_id":["R::1","R::3"]},
{"user_id":"user4","rel_ids":["R::1","R::2","R::3"]}
]

geraldss · May 24, 2016, 6:21pm

Array index is for the WHERE clause. So you can always use an array index if you have a WHERE clause with a matching ANY, ANY AND EVERY, or UNNEST.

Your first output can be achieved with UNNEST.

Your second output can be achieved with

SELECT ARRAY_AGG(id) AS rel_ids, ...
...
GROUP BY user_id;

Topic		Replies	Views
Index on array inside array Couchbase Server query , n1ql , index	1	612	January 7, 2022
How to properly index array field? Couchbase Server n1ql , index	3	1857	June 21, 2017
Index join using an array index SQL++	8	2367	April 20, 2018
Indexing arrays in Couchbase 4.1 SQL++ n1ql	4	1616	August 9, 2016
N1ql join by array SQL++	7	2543	September 1, 2016

Array Index Question

Related topics