SLow N1QL query

SQL++
1

I have one which takes some around 35-40 ms on my local machine but take more than 500 ms on live machine.

Following is the query

SELECT pin FROM activity USE INDEX (activity_pin_report) UNNEST activity.pins AS pin WHERE pin.pin_type=“report” AND
pin.place_id=‘MMI000’ AND pin.status=1 ORDER BY pin.user_name=‘shashankkapsime’ DESC
and uses following indexing

CREATE INDEX activity_pin_report ON activity((distinct (array (pin.place_id) for pin in pins when (((pin.pin_type) = “report”) and ((pin.status) = 1)) end)))

What else need to be considered for this

2

It’s most likely the unnest. Have tried just indexing the pins property? I found with arrays, that tends to be most effective. conditions for the index should be about the array rather than properties on the objects in the array. ARRAY_LENGTH(pins) > 0 if that would apply.

make sure the index covers the query as well.