Couchbase performance issues when scanning bucket for certain documents - Getting timeout exception

Question

We have a Couchbase server version Community Edition 5.1.1 build 5723

In our Cars bucket we have the Car Make and the Cars it manufactured.

The connection between the two is the Id of the Car Make that we save as another field in the Car document (like a foreign key in a MySQL table).

The bucket has only 330,000 documents.

Queries are taking a lot of time - dozens of seconds for very simple query such as

select * from cars where model="Camry"  <-- we expect to have about 50,000 results for that

We perform the queries in 2 ways:

1. The UI of the Couchbase
2. A Spring boot app, that constantly get a TimeOutException after 7.5 seconds

We thought the issue is a missing index to the bucket.

So we added an index:

CREATE INDEX cars_idx ON cars(makeName, modelName, makeId, _class) USING GSI;

We can see that index when running

SELECT * FROM system:indexes

What are we missing here? Are these reasonable amount of times for such queries in a NoSQL DB?

Answer 1

Try

CREATE INDEX model_idx ON cars(model);

Your index does not cover model field.

And you should have index for spring data couchbase "_class" property

CREATE INDEX `type_idx` ON `cars`(`_class`)

Answer 2

So, this is how we solved the issue:

1. Using this link and @paralen answer, we created several indexes that speed up the queries.
2. We altered our code to use pagination when we know the returned result set will be big, and came up with something like this:

    do{
       Pageable pageable = PageRequest.of(pageNumber, SLICE_SIZE, Sort.by("id"));
       Slice slice carsRepository.findAllByModelName("Camry", pageable);
       List cars = slice.getContent();
    } while (slice.hasNext());