In clause taking time

SQL++
1

The IN clause is taking too long when the N1QL is executed using the query workbench.

N1QL

explain SELECT * FROM pmc as pmc1 where meta().id IN (select raw meta().id from pmc order by meta().id LIMIT 5)

Explain plan

{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan2”,
“index”: “pmc”,
“index_id”: “23c40e86cdef1996”,
“keyspace”: “pmc”,
“namespace”: “default”,
“spans”: [
{
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“as”: “pmc1”,
“keyspace”: “pmc”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((meta(pmc1).id) in (select raw (meta(pmc).id) from pmc order by (meta(pmc).id) limit 5))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM pmc as pmc1 where meta().id IN (select raw meta().id from pmc order by meta().id LIMIT 5)”
}

Time taken for returning 5 documents

elapsed: 33.57s | execution: 33.57s | count: 5 | size: 2623

When I get the raw meta ID’s from a prior N1QL using java and then pass the JSON array of ID’s to to second N1QL IN clause, it runs withing below 1 sec

public List getAllProductsbyN1ql(int skip, int limit, String keyPrefix) {
// Get the meta id for the records
Statement statement = Select.select(“meta().id”)
.fromCurrentBucket()
.where(x(“meta().id”).like(s(keyPrefix + “%”)))
.orderBy(Sort.asc(x(“meta().id”)))
.limit(limit)
.offset(skip);
System.out.println(statement.toString());
//N1qlQueryResult n1qlQueryResult = template.getCouchbaseBucket().query(statement);

  List<String> meta_ids = template.getCouchbaseBucket().query(statement).allRows().stream().map(sc -> sc.value().get("id").toString()).collect(Collectors.toList());
  statement = Select.select("*")
  		.fromCurrentBucket()
  		.where(x("meta().id").in(JsonArray.from(meta_ids)))
  		.orderBy(Sort.asc(x("meta.id")));
  System.out.println(statement.toString());
  return template.getCouchbaseBucket().query(statement).allRows();

}

2

Query optimization is done during prepare time and right side of the IN clause is not available till execution. So Optimizer as consider all possible values. So it does complete index scan and applies filters.

Option 1) Execute subquery and use SDK to fetch the documents
Option 2) Execute subquery get results and execute second query adhoc=true
Option 3)
SELECT * FROM pmc as pmc1 USE KEYS (select raw meta().id from pmc order by meta().id LIMIT 5)
Option 4) SELECT * FROM pmc WHERE META().id IS NOT NULL ORDER BY meta().id LIMIT 5;