Slow Document lookup after upgrade when using N1QL WHERE

I am seeing a strange behavoir when using the GUI since i upgraded. Main issue is seed, if i use the below query in my Query Editor it takes 8.1 ms.

select * from Contacts where _type = ‘user’

now if i do the ame in the Doc Viewer and apply the filter _type=‘user’ the same query takes over 10 seconds/ Is this expected or what do i need to change to get this somewhat responsive again ?

Just as FYI if i look up doc by its key it is normal sub sec just any N1QL where clauses cause the issue

Hi @aponnath,

Could you please specify the version of Couchbase server that you are facing the issue with? Also, which was the version you were running prior to the upgrade?

Currently its Enterprise Edition 7.1.0 build 2556 not sure what it was before but i think 7.0.3 and its running on ubuntu 20.0.4.

After some more tests it seems that the issue is related to one bucket. Others don’t have this issue . I also rebuild the Index for _type and whats interesting is that if i use something else like contact or so its way faster.

Here is a video of this Dropbox - 2022-06-14_09-53-49.mp4 - Simplify your life

As you seen your screen shot DocViewer using primary index that why it takes time vs Query Viewer uses secondary index on _type. cc @eben

Was there a change tat caused that ?because in older version that was not the case, i also see sometimes that there is no index avail.

Also i dropped my #primary and rebuild it and didn’t change anything

@aponnath The document viewer needs to page through the documents in order of document ID, so it uses a query like:

select meta().id 
from Contacts._default._default use index(`primary`) 
where type = "_user" 
order by meta().id 
limit 10 offset 0;

How long does that query take on your system?

I suspect that it’s using the primary index instead of the type index.

Does the query return the same results faster if you remove the “use index” clause? That was added in 7.1 because it improved performance in some cases.

Below is the results of the Query’s as per your request. I am not sure why the primary index is so bad for this bucket. What does it actually index ? If you have a where clause wouldn’t it be smarter for the viewer to use the indexes dynamically if the user does not specify’ s one just like normal N1QL.
What is also strange is that i have buckets with lots more docs several million and that is way faster when i do a _type filter in the doc viewer. I rebuild my primary index a few times with no luck

Ok i ran this Query

SELECT META().id
FROM Contacts._default._default USE INDEX(`primary`)
WHERE _type = "user"
ORDER BY META().id
LIMIT 10
OFFSET 0;

and that came back with these nbr’s 16.1s 7 docs 462 bytes

Index Currently Used

CREATE PRIMARY INDEX primary ON default:Contacts._default._default

if i use

SELECT META().id
FROM Contacts._default._default
WHERE _type = "user"
ORDER BY META().id
LIMIT 10
OFFSET 0;

and that came back with these nbr’s 5.3ms 7 docs 462 bytes

Index Currently Used

CREATE INDEX contacts_type ON default:Contacts._default._default(_type)

The primary index holds only the document keys. So full documents are retrieved in order to evaluate the filter on the _type field. It would depend on how many documents have to be retrieved - in key order - to find the 10 (limit) that match the filter.

So if for example one bucket’s keys for documents with the _type = “user” start with the letter ‘a’, and another’s start with ‘z’ (and assuming an equal number of documents of equal size) you should be able to see that the first will be able to qualify the 10 needed for the query with reading fewer documents overall as it can stop once the limit is reached.

HTH.

I get the Point but it defeat’s the purpose that the viewer uses the primary index vs the best index like the N!QL Query Tool. If N1QL can use the best Index why cant the viewer. That’s like having a Tesla with Auto pilot and instead of using the best route in Self Drive Mode you drive a 50 mile detour.

But like i said this got worse in 7.1 in 7.0 this was not an issue

I get your frustration. I think @eben covered that with “That was added in 7.1 because it improved performance in some cases.” but would have to defer to Eben as to what those cases were and why/what could be done to help your case.

If there is a performance benefit then there should be either be an option to use the primary or let the system decide on the best index to use, because 16 sec for a query is not something to be proud of specially when index exists to do it in 5 ms

@aponnath I got approval for a fix for the behavior you are seeing, it should appear in release 7.1.2. In the mean time, my apologies for the slow performance in the UI.

No worries, it is nothing major for me as i use the query tool most of time. I just feel taht this might be a step backwards then forward. Hopefully there will be a way to do both via a check box or so in the viewer that will allow user to either use the primary to get the benefits you say there might be or to use the default behavior to use the most effective index. That way everyone can have both without being exclusive