Advantages of partial indexes


#1

Hi, I was reading this slideShare https://es.slideshare.net/journalofinformix/couchbase-n1ql-index-advisor and I was curious about partial Indexes. Here say “every couchbase index should be a patrial index except primary”. WHY? For example, what advantages i get with this index (A) versus (B):

(A) CREATE INDEX idx_a ON CONTENT(type, brand, status)
(B) CREATE INDEX idx_b ON CONTENT(status) where type = “car” and brand = “Toyota”

when i do queries like this:
Select * from CONTENT where type = “car” and brand = “Toyota” and status = “AVAILABLE” and country = “USA”

I’m pro partial indexes, but i would like to know a solid argument about it.


#2

Couchbase bucket can contain unrelated documents. When you create index issue query you normally query based on related documents.
Index B is leaner, it takes less Disk and less I/O.
If you need to create different index for each value then Index A is better.
If the Index is too big Index A may not fit in Memory(for MOI) or or can’t fit on single machine, Index B is better.

Partial Index is same concept has partition clauses as RDBS but manually created.