Poor query performance with count(*) and JOIN

SQL++
1

I have 2 versions of a query 1 is performant and another is taking time(17s to get 10k result count)
Query 1:(Taking 17s for 10k records-dedicated index node)
SELECT count(*)
FROM b1
INNER JOIN b2
ON b2.Id =meta(b1).id
WHERE b1.type=‘type1’
AND b2.type=‘type2’
AND b1.x = “test”
AND b1.y IN [“test”,“test2”]
AND b1.z IS NOT NULL
AND b1.z. <> ‘’
AND b1.z1 IS MISSING
AND IFMISSINGORNULL((FIRST x FOR x IN ARRAY_REVERSE(b2.field[0].field)
WHEN x.p <> b1.p END).user.id <> ‘1234’, TRUE)= TRUE

Query 2:(responding in milliseconds)
SELECT meta(b1).id
FROM b1
INNER JOIN b2
ON b2.Id =meta(b1).id
WHERE b1.type=‘type1’
AND b2.type=‘type2’
AND b1.x = “test”
AND b1.y IN [“test”,“test2”]
AND b1.z IS NOT NULL
AND b1.z. <> ‘’
AND b1.z1 IS MISSING
AND IFMISSINGORNULL((FIRST x FOR x IN ARRAY_REVERSE(b2.field[0].field)
WHEN x.p <> b1.p END).user.id <> ‘1234’, TRUE)= TRUE
LIMIT 1

2

The LIMIT clause in the second query means it only has to find the first result then it stops processing, so hardly comparison to draw any conclusions from. But milliseconds to return just one result does sound like it is taking too long to perform well over a larger result set.

( I assume a small cut-n-paste error in the text and “.user.id” is intended to be “b1.z1.user.id” ? )

What is the query plan? If you’re using EE, can you set profile=timings and see where the time is spent in the plan? Does the index advisor suggest any alternative indexes? ( https://index-advisor.couchbase.com/indexadvisor )