Explain on query with subquery

When attempting explain on query with subquery, I note that the explain plan does NOT explain the subquery.
With that I would like to know if it is correct to assume that I can somewhat piece together the plan if i were to

explain select raw COUNT(number) from default where id = 1234
explain select * from data where (select raw COUNT(number) from default where id = 1234)[0] = 1

E.g.
explain select * from data where (select raw COUNT(number) from default where id = 1234)[0] = 1

Result as follow

{
“requestID”: “b0bf4871-e1d6-4262-a69f-b6ecf647600a”,
“clientContextID”: “372f5494-b79c-4f56-acfa-42bfe36656ee”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “data”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “data”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((select raw count((default.number)) from default where ((default.id) = 1234))[0]) = 1)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select * from data where (select raw COUNT(number) from default where id = 1234)[0] = 1”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “13.181418ms”,
“executionTime”: “13.134162ms”,
“resultCount”: 1,
“resultSize”: 1950
}
}

At present EXPLAIN only includes FROM clause subquery plan, not the subqueries present in other places (improvement https://issues.couchbase.com/browse/MB-21936) . Yes, you can run the EXPLAIN on subquery independently.

Hi @wai.kwang.mak,
Note that this method of stitching individual query plans may is good to get rough understanding… but may not be completely accurate reflection of the query execution plan. For example, the plan may be valid for non-correlated queries, but the subquery is evaluated only once (as opposed to correlated subqueries where the subquery is evaluated once for each doc in the parent query).

For correlated subqueries, real execution is similar to JOINs, but that won’t be reflected by stitching individual plans. Refer to https://developer.couchbase.com/documentation/server/4.6/n1ql/n1ql-language-reference/correlated-subqueries.html for more details.

Indeed, the lack of accurate explain of query with subquery is a concern. It prevents the developers from making informed decision.

Is there any plans to address explain on query with subquery in the coming release?

You can track the improvement https://issues.couchbase.com/browse/MB-21936
N1QL is rule based optimizations. For correlated subqueries replace subquery with query parameter and do EXPLAIN on subquery it will give the right plan.