Huge performance difference with filtering on joined document

N1QL
1

I ran two queries listed as below:

Query 1:

select A, B from data2 A
left join data2 B on keys A.bId
where A.type="a"
and (B.tags.[“something”]=“somevalue”)

Query 2:

select A, B from data2 A
left join data2 B on keys A.bId
where A.type="a"
limit 1

The difference is that Query 2 doesn’t have this filter condition “and (B.tags.[“something”]=“somevalue”)”. There are 800k document of type A and one document of type B. There is one document of type A associated with document type B so both queries will return only one result.

Index is built for “type” field

create index type_index on data2(type) using gsi

Also index is build for “bId” but I think it won’t be used because I am not using indexed join.

Now the problem is, Query 1 takes 9s to execute, while Query 2 only take 200ms. Isn’t it filtering is applied after fetch operator and join operator? From “explain” statement, the difference between these two queries is the filter operator, how come the performance difference is so significant?

2

Why are you doing a left join?

3

Strange that now both queries runs 9 seconds. Replacing left join with join doesn’t help (sorry that I simplified the query example and forget to remove the left join). But I think the original problem is not reproducible now.