if i queried with normal gsi , there is results. but using array indexing, there is no.
only i changed ‘use index’ statement.
i wanna know what is checkpoint?
could you share your index definitions and queries and the version of couchbase server you are using?
thanks
-cihan
Thank you so much for your reply fast
Index 1: CREATE INDEX `PLAYHISTORY_TABLEID_IDX` ON `PlayHistory`(`tableId`,array (`p`.`sessionId`) for `p` in `players` end,`timestamp`) WITH({"index_type" : "forestdb"})
Index 2: CREATE INDEX `PLAYHISTORY_GPID_IDX` ON `PlayHistory`((distinct (array (`t`.`userName`) for `t` in (`PlayHistory`.`players`) end)),`timestamp`,`playType`) WITH({"index_type" : "forestdb"})
query 1: SELECT p.*
FROM PlayHistory
USE INDEX(`PLAYHISTORY_TABLEID_IDX`)
unnest PlayHistory.players as p
WHERE PlayHistory.tableId ="0000000a-627c-571d-c900-0000ea050000"
and ANY t IN PlayHistory.players SATISFIES t.userName = "${userName}" END
and p.userName = "${userName}"
query 2: SELECT p.*
FROM PlayHistory
USE INDEX(`PLAYHISTORY_GPID_IDX`)
unnest PlayHistory.players as p
WHERE PlayHistory.tableId ="0000000a-627c-571d-c900-0000ea050000"
and ANY t IN PlayHistory.players SATISFIES t.userName = "${userName}" END
and p.userName = "${userName}"
version: 4.5 DP
Hope you have a wonderful day.
Best regards
Please also post the EXPLAIN for both queries.
query 1:
[
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "PLAYHISTORY_TABLEID_IDX",
"keyspace": "PlayHistory",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"0000000a-627c-571d-c900-0000ea050000\")"
],
"Inclusion": 1,
"Low": [
"\"0000000a-627c-571d-c900-0000ea050000\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Unnest",
"as": "p",
"expr": "(`PlayHistory`.`players`)"
},
{
"#operator": "Filter",
"condition": "((((`PlayHistory`.`tableId`) = \"0000000a-627c-571d-c900-0000ea050000\") and any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"${userName}\") end) and ((`p`.`userName`) = \"${userName}\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`p`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
]
query 2:
[
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan",
"index": "HANDHISTORY_GPID_IDX",
"keyspace": "PlayHistory",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"${userName}\""
],
"Inclusion": 3,
"Low": [
"\"${userName}\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Unnest",
"as": "p",
"expr": "(`PlayHistory`.`players`)"
},
{
"#operator": "Filter",
"condition": "((((`PlayHistory`.`tableId`) = \"0000000a-627c-571d-c900-0000ea050000\") and any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"${userName}\") end) and ((`p`.`userName`) = \"${userName}\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`p`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
]
whether there is unnest statement or not, same result.
how can i troubleshooting?
Hi @donovan.kim,
In query 2, please make the following two changes, in two places:
(1) Remove quotes around “${userName}”
(2) Remove {} around ${userName}
In both places, the final parameter should be $userName.
Currently, you are using a string literal instead of a parameter.
it is just my editing.
originally there is just string literal.
Can you post the exact query2 as entered into cbq shell?
cbq> SELECT PlayHistory.* FROM PlayHistory USE INDEX(`PLAYHISTORY_GPID_IDX`) UNNEST PlayHistory.players AS p WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "301217bbafa15c33" END AND p.userName = "301217bbafa15c33" ;
{
"requestID": "03b77d05-aea8-42b1-a009-bf3797e4f975",
"signature": {
"*": "*"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "7.153169ms",
"executionTime": "7.039194ms",
"resultCount": 0,
"resultSize": 0
}
}
Ok. Can you also post the EXPLAIN output for the above query2.
Also, can you post the output of query3, which is exactly like query2, but with use index `#primary`, to verify the results.
cbq> explain SELECT PlayHistory.* FROM PlayHistory USE INDEX(`PLAYHISTORY_GPID_IDX`) UNNEST PlayHistory.players AS p WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END AND p.userName = "rnVHZOQLutmH}
{
"requestID": "50fb61cb-6694-4100-9ad8-479eb1698a03",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan",
"index": "PLAYHISTORY_GPID_IDX",
"keyspace": "PlayHistory",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"rnVHZOQLutmH8nD0duoxeuOG\""
],
"Inclusion": 3,
"Low": [
"\"rnVHZOQLutmH8nD0duoxeuOG\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Unnest",
"as": "p",
"expr": "(`PlayHistory`.`players`)"
},
{
"#operator": "Filter",
"condition": "(any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end and ((`p`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`PlayHistory`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "8.278298ms",
"executionTime": "8.205878ms",
"resultCount": 1,
"resultSize": 2702
}
}
cbq> explain SELECT PlayHistory.* FROM PlayHistory USE INDEX(`#primary`) UNNEST PlayHistory.players AS p WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END AND p.userName = "rnVHZOQLutmH8nD0duoxeuOG}
{
"requestID": "3f753dc7-9e89-481f-bf00-ad27fdd2c77d",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "PlayHistory",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Unnest",
"as": "p",
"expr": "(`PlayHistory`.`players`)"
},
{
"#operator": "Filter",
"condition": "(any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end and ((`p`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`PlayHistory`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "2.513306ms",
"executionTime": "2.388315ms",
"resultCount": 1,
"resultSize": 1888
}
}
cbq> SELECT PlayHistory.* FROM PlayHistory USE INDEX(`#primary`) UNNEST PlayHistory.players AS p WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END AND p.userName = "rnVHZOQLutmH8nD0duoxeuOG";
{
"requestID": "c36d5b12-5538-4b16-b3e6-ea0ee7e774fd",
"signature": {
"*": "*"
},
"results": [
~~~ Deleted ~~~
],
"status": "success",
"metrics": {
"elapsedTime": "56.882164ms",
"executionTime": "56.735879ms",
"resultCount": 15,
"resultSize": 246693
}
}
cbq>
without unnest
cbq> explain SELECT PlayHistory.* FROM PlayHistory USE INDEX(`PLAYHISTORY_GPID_IDX`) WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END ;
{
"requestID": "8aaf0489-434a-46d5-9392-da0c949a2d02",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan",
"index": "PLAYHISTORY_GPID_IDX",
"keyspace": "PlayHistory",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"rnVHZOQLutmH8nD0duoxeuOG\""
],
"Inclusion": 3,
"Low": [
"\"rnVHZOQLutmH8nD0duoxeuOG\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`PlayHistory`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "3.235177ms",
"executionTime": "3.164821ms",
"resultCount": 1,
"resultSize": 2419
}
}
cbq> SELECT PlayHistory.* FROM PlayHistory USE INDEX(`PLAYHISTORY_GPID_IDX`) WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END ;
{
"requestID": "2f963229-3b31-4e77-9145-4ddfc23b53ab",
"signature": {
"*": "*"
},
"results": [
],
"status": "success",
"metrics": {
"elapsedTime": "5.99479ms",
"executionTime": "5.921861ms",
"resultCount": 0,
"resultSize": 0
}
}
cbq> explain SELECT PlayHistory.* FROM PlayHistory USE INDEX(`#primary`) WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END ;
{
"requestID": "12ae1abf-d612-49a4-a6b7-4524c8c3272f",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "PlayHistory",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"keyspace": "PlayHistory",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`PlayHistory`",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "2.364023ms",
"executionTime": "2.285401ms",
"resultCount": 1,
"resultSize": 1605
}
}
cbq> SELECT PlayHistory.* FROM PlayHistory USE INDEX(`#primary`) WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END ;
{
"requestID": "0cc8dcce-a0b1-41e3-95b2-827ee6c6d945",
"signature": {
"*": "*"
},
"results": [
~~~ DELETED ~~~
],
"status": "success",
"metrics": {
"elapsedTime": "254.380493ms",
"executionTime": "254.286247ms",
"resultCount": 15,
"resultSize": 246693
}
}
i think array indexing is not working.
i wanna know what is the starting point of trouble shooting.
Looks like a possible bug in 4.5 DP. We use and test array indexing, but your data may have surfaced a bug. Can you please test with the upcoming Beta in a few days? If you cannot wait for a few days, we can look into getting you a candidate build. Let us know, thanks.
of course, i can wait a few days.
and i wanna get a candidate build, too.
what shall i do?
Ok. Let’s try the Beta first in a few days, thanks.
i installed 4.5 beta. but still exists same problem.
Please try to drop the index and re create with following statements.
In your create index statement you used bucket name in the array index portion. Which is not required.
drop index `PlayHistory`.`PLAYHISTORY_GPID_IDX`;
CREATE INDEX `PLAYHISTORY_GPID_IDX` ON `PlayHistory`((distinct (array (`t`.`userName`) for `t` in `players` end)),`timestamp`,`playType`) WITH({“index_type” : “forestdb”});
i am on holiday. i will try it 9, May. thanks.
1 Like