Hi, the below query is showing intersect scan in explain plan (When providing index hint, query works fine). The query is: SELECT Count(*) FROM reporting rgm WHERE rgm.type = "DocumentStatus" AND meta(rgm).id NOT LIKE "_sync%" AND ANY x IN [lower(rgm.fullName),lower(rgm.lastName)] SATISFIES x l…

Query optimizer uses rule based optimizations. When query is completely covered it avoids InetsectScan. Otherwise it uses all qualified indexes and does InterSect Scan. In your case there is array index and it is with IN clause (more than one span) so it is not covered. Try the following index (…

Query plan showing intersect scan even after creating proper index. (CB- 4.6.4-4590-enterprise)

SQL++

krishan.jangid March 30, 2018, 3:56pm 3

The leading key (verificaitonStatus) is not going to be used for equality predicate. There will always be ‘IN’ clause for verificationStatusCode. However, changing its position in the index statement works.

Topic		Replies	Views
Why is the query intersecting indexes when one index has all it needs? SQL++ query	4	1004	March 7, 2019
Indexing not working as expected SQL++ n1ql , index	9	1531	December 11, 2017
Slow query speed even with intersect scan of two indexes, high query time variability SQL++	3	979	November 20, 2020
How can an Intersect Scan return more items than MIN(inputs from respective indices). It's returning the MAX SQL++ index	6	2010	March 12, 2020
What does it mean when index scan does not show inItems and outItems, when query is picking up multiple indexes? SQL++	2	906	June 9, 2022

Query plan showing intersect scan even after creating proper index. (CB- 4.6.4-4590-enterprise)

Related topics