Hello, I am trying to understand why the following n1ql query works 10x times slower than its subqueries individually.
CREATE INDEX `ix_id_store_type` ON `maindb`(`id`,`store_type`)
LET cid = (
SELECT RAW m.contract_id
FROM maindb AS m
WHERE m.store_type = 'hotel'
AND m.id = 330
WHERE maindb.store_type = 'contract'
AND maindb.id == cid
I suppose that secondary index
ix_id_store_type is not used while executing subquery (by the way i’m not sure that EXPLAIN shows index usage in subquery properly) and I have no idea why.
Running Couchbase version:
Version: Community Edition 5.0.0 build 3519
Below is an example of query plan execution time with “PLAN”.
When you do your query in the workbench you can see the times, % of time , if it uses an index or not.
At present EXPLAIN only includes subquery plan if the subquery present in the FROM clause.
If subquery is present in other parts of query EXPLAIN doesn’t include subquery EXPLAIN (MB-21936 .
If the subquery is not correlated you can do standalone on EXPLAIN on subquery to check plan and same plan will be used by parent query.
Try the following index.
The parent query predicate maindb.id == cid , as cid is dynamic the query needs to scan all ids, fetch documents and apply predicate. You can enable profile=timings and check timings and In/Out records for each operator and tune accordingly.
You can also try as 2 separate queries. i.e. execute subquery and pass the result as query parameter to second query. Based on your query this will perform much better.