Secondary Index Filtering Performance

SQL++
1

I have a Couchbase server with one bucket that contains multiple types of documents, each containing a documentType field to denote each document’s type.

I’ve setup GSI indices to cover pretty much all of my WHERE clauses and I realized that some of them are document-type specific. For example:

CREATE INDEX timeslotDose_index ON default(documentType,userId,timeslotDose,endDate) USING GSI

In this case, the timeslotDose field will only be in documents where their documentType is medication. So I was wondering if there would be any performance gain for queries using the index by adding a filtering WHERE clause (maybe even remove the documentType field from the index?), for instance:

CREATE INDEX timeslotDose_index ON default(documentType,userId,timeslotDose,endDate) WHERE documentType = “medication” USING GSI

How would you reckon the filtering would affect performance in such a case?

Any help would be appreciated.

Thanks,
Itamar.

1 Like
2

Great question. Yes, please add the WHERE clause. Put the most selective keys at the beginning of the index key list. Also retain documentType somewhere in the index key list.

1 Like
3

I’ve never given too much thought to the order of the keys, great advice. Cheers.