Why is this Query performing a fetch

SQL++
,
1

Need help understanding when and why the query engine executes a fetch.

I have (for example) a simple query:

SELECT docType
FROM bucket
WHERE `docType’ = “employee” and employeeType = ‘supervisor’ and employmentStartDate > 1568310951

The plan scans two indices defined on “app” like so:

ON bucket(docType,employeeType) WHERE ((docType = “employee”) and (empolyeeType = “supervisor”))

and

ON bucket(docType,employeeStartDate)

Thus seemingly everything for the filtering and the projection is available from the indices.

The plan then does an intersect (good) and then a FETCH. The fetch constitutes 83% of the query run time.

This query is just a sample, but I’m seeing this a lot and am obviously doing something wrong.

Please tell me why it’s doing a fetch and how to avoid it. Really need to understand this to query effectively, please help.

2

If query covered by single index it avoids Fetch.
If query is not covered by single index and more than one index qualifies it does IntersectScan by using all qualified indexes. In that situation query must fetch the document even though all indexes together has required fields.

Reason: Couchbase indexes are maintained asynchronously, each index might be using different snapshot of time.
By combining different sources query can’t produce results. It must fetch keep the document consistent.

Checkout Covered Intersect Scan with Adaptive Index

1 Like