N1QL query with IN Clause - not working

SQL++
, ,
21

@vsr1,

Thank you m working on that,let me resolve this with your suggestion.

apart of new query you wrote that seems great but the issue with me is that i need to wote them using java SDK,
so it would be more appreciated if you guide me on that. or any other link .

Thanks & Regards,

22

@vsr1 and @subhashni : below is my actual query and the explain query result.

explain select * from dp_token where doc_type=‘token’ and (accnt_guid in [“a1b3594f-0b76-4c54-8206-db2c16286320”])

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “idx_accnt_guid_trid”,
“index_id”: “44c80a027848811d”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(“a1b3594f-0b76-4c54-8206-db2c16286320”)”
],
“Inclusion”: 1,
“Low”: [
"“a1b3594f-0b76-4c54-8206-db2c16286320"”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_doc_type1”,
“index_id”: “9389bc3bed8ba95f”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“token”"
],
“Inclusion”: 3,
“Low”: [
"“token”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_accnt_guid1”,
“index_id”: “1e3b8154e41967d6”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“a1b3594f-0b76-4c54-8206-db2c16286320"”
],
“Inclusion”: 3,
“Low”: [
"“a1b3594f-0b76-4c54-8206-db2c16286320"”
]
}
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Fetch”,
“keyspace”: “dp_token”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((dp_token.doc_type) = “token”) and ((dp_token.accnt_guid) in [“a1b3594f-0b76-4c54-8206-db2c16286320”]))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select * from dp_token where doc_type=‘token’ and (accnt_guid in [“a1b3594f-0b76-4c54-8206-db2c16286320”])”
}
]

23

Hi parth.j.shah,

The query that you provided should have worked. I need the explain from the JAVA program where it fails.

24

The query doesnt fail , but just doesnt return any result. What exactly do you want ?

25

If you want use new query replace old query with new one and rest should work.

26

Can you try the following in cbq shell. replace default with your bucket and tell me if gives results.

PREPARE p1 AS SELECT * FROM default WHERE doc_type=‘xyz’ AND guid IN $guid_list;
\set -$guid_list [“a1b3594f-0b76-4c54-8206-db2c16286320”] ;
execute p1;
If that doesn’t give results.
EXPLAIN SELECT * FROM default WHERE doc_type=‘xyz’ AND guid IN $guid_list;

Also Tell me Version of Couchbase Server you are using.

27

Hi @vsr1 - I am using 4.5 version,
And execute p1 didnt return any result for long long time and at last i quit and did the explain.

EXPLAIN SELECT * FROM dp_token WHERE doc_type=‘token’ AND accnt_guid IN $guid_list;
{
“requestID”: “1decf1fa-09cb-45ce-8bc6-2d2c21991f2e”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “idx_accnt_guid_trid”,
“index_id”: “44c80a027848811d”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“Inclusion”: 0,
“Low”: [
“null”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_accnt_guid1”,
“index_id”: “1e3b8154e41967d6”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“Inclusion”: 0,
“Low”: [
“null”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_doc_type1”,
“index_id”: “9389bc3bed8ba95f”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“token”"
],
“Inclusion”: 3,
“Low”: [
"“token”"
]
}
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Fetch”,
“keyspace”: “dp_token”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((dp_token.doc_type) = “token”) and ((dp_token.accnt_guid) in $guid_list))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM dp_token WHERE doc_type=‘token’ AND accnt_guid IN $guid_list”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “8.244769ms”,
“executionTime”: “8.193804ms”,
“resultCount”: 1,
“resultSize”: 4462
}
}

28

You have many qualified indexes it using the IntersectScan. This been improved later version.

NOTE: guid IN $guid_list; is not optimal. Because query doesn’t know the values at plan generation time it generates plan based on all guid values. This may take longer time based on how many documents qualify for other predicates.

Please try this.
PREPARE p2 AS SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list;
\set -$guid_list [“a1b3594f-0b76-4c54-8206-db2c16286320”] ;
execute p2;

29

@vsr1:
This didnt work either.

30

Can you more specific. you did not get any results. or it take long time.

31

Didnt get any result.

32

What is explain output. Provide me sample document where you expecting results

33

Hi @vsr1 - Here’s the explain output along with my Prepare statement -

PREPARE p14 AS SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list;
{
“requestID”: “6755a0ff-6737-4874-b788-89b8c33786ed”,
“signature”: “json”,
“results”: [
{
“encoded_plan”: “H4sIAAAJbogA/5xTYW/TMBT8K9YDaU0VIVUFaa20DxVLtUpjVA0IJDalrv2Wmrl2sB3omMJv5zksLe34wr7Zl3vnu7PzAGiElSiLSnMDY4AUDN8grarBa9rYCh0P1sH4AV7sN5Djt5pGkSi/xFpp6ZDGvxySJnVYW6d+RlZLepbKzEjc5oLspaDimjAlt4WsimDv0BTSiiLcVzjoCIWik2A0PB2txHCF8nTFR29u6esd3vuKixivm34MvIPxltc6EEqI8a2ZBTclRusXqlwTAtfQjl4D3KRkT+jaK0vGhylc2h9HjKYhEhFMSfKlV9Ckh/mmGMT6v8wdK8y541qj3tX4rJ6nSgd08aqskSq0iaDX6y07N8tXy67qZcLO2C5kwriR7JDJhTChKGsliasMexmXhVY+JMmTADND53E9d/Yrili+Q09BC/Kz+XMHuK0i0aOO9+gDp11wNTY3T+pUZq/UxPqbFCqnviuNJfpYzWOP413N40FzLJMHh3wD7bRXpeGhdu0j6NPHPiWAgNsQy19k88kiY/S/sEnO8uwye/uB9dl08f4d605gH/OMza7Os8+9fz/dhH26yEilA85OWsIJm1yds32XpPFXk9D8DgAA///leP+7wAMAAA==”,
“name”: “p14”,
“operator”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Authorize”,
“child”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_doc_type1”,
“index_id”: “9389bc3bed8ba95f”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“token”"
],
“Inclusion”: 3,
“Low”: [
"“token”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “dp_token”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((dp_token.doc_type) = “token”) and ((dp_token.accnt_guid) in $guid_list))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“privileges”: {
“default:dp_token”: 1
}
},
{
"#operator": “Stream”
}
]
},
“signature”: {
"": ""
},
“text”: “PREPARE p14 AS SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.142817ms”,
“executionTime”: “3.086442ms”,
“resultCount”: 1,
“resultSize”: 4300
}
}
cbq> EXPLAIN SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list;
{
“requestID”: “4b04493f-34d2-42a4-9c49-5b16e6528b3d”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx_dp_token_doc_type1”,
“index_id”: “9389bc3bed8ba95f”,
“keyspace”: “dp_token”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“token”"
],
“Inclusion”: 3,
“Low”: [
"“token”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “dp_token”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((dp_token.doc_type) = “token”) and ((dp_token.accnt_guid) in $guid_list))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “2.312439ms”,
“executionTime”: “2.233868ms”,
“resultCount”: 1,
“resultSize”: 2430
}
}

34

please provide sample document that you expecting results. interested only fields that in predicate

35

do you mean the sample response document itself ? I wont be able to share the sample document. Is there a reason you are interested in the sample response document ?

36

I don’t need whole document. need to know the doc_type, accnt_guid of response document.

Do you get results for this query.

SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid = “a1b3594f-0b76-4c54-8206-db2c16286320”;

37

@vsr1 If i use above query even in my query work bench - its not returning me result. - NOTE : It took 4m - i presume max time on query work bench. If i do USE INDEX(idx_accnt_guid_trid) - its faster, but again nothing is working from Java client.

But if i remove the USE INDEX - it returns result in about 150 ms.
SELECT * FROM dp_token WHERE doc_type=‘token’ AND accnt_guid = “a1b3594f-0b76-4c54-8206-db2c16286320”; - this returns result in 150 ms.

for your above question - the values are already in teh query -

doc_type ='token’
accnt_guid=‘a1b3594f-0b76-4c54-8206-db2c16286320’

38

When you remove the USE INDEX it using index on accnt_guid. that is why it faster.

SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid = “a1b3594f-0b76-4c54-8206-db2c16286320”;
You mentioned it is taking time.
Not sure how following query finished so quickly
PREPARE p2 AS SELECT * FROM dp_token USE INDEX(idx_dp_token_doc_type1) WHERE doc_type=‘token’ AND accnt_guid IN $guid_list;
\set -$guid_list [“a1b3594f-0b76-4c54-8206-db2c16286320”] ;
execute p2;

39

@vsr1

this is the result for that -

“status”: “success”,
“metrics”: {
“elapsedTime”: “3m44.203143157s”,
“executionTime”: “3m44.203065809s”,
“resultCount”: 2,
“resultSize”: 3891
}

40

Ok. Query works. Now you can put USE INDEX hint in java program try it. If you have any timeouts in SDK you need to increase them. Please also track all errors/timeouts. Each will have different meaning.