Basically a primary index allows you to only find KEYS any sort of N1QL DML statement would then always need to fetch the document to do filtering this is much less efficient than a purpose built index that satisfies a query without needed to dip into KV.
Primary indexes essentially have to scan across all documents in a bucket, you almost never would want to do this for a production OLTP Database workload as it would use an excessive amount of resources and wouldn’t provide ideal latency. The size of a primary index grows with each document in the bucket, which might be into the billions with a production use-case. If your production cluster uses defined indexes on specific fields, these are normally much smaller than indexing every document in a bucket and therefore are less resource intensive. When you write a N1QL query for specific fields and the cluster has to use the primary index, it forces the Query Service to make additional requests to the Data Service nodes and transfer the documents from the buckets to satisfy the request. These additional hops and data transfers can be slow depending on your infrastructure and use-case.
Primary indexes are optional. When a query can be served with just indexed fields, this is called a covering index. Covering indexes are serviced very quickly as they do not need to go back to the Data Service to collect additional information before a query is serviced. And when using memory optimised indexes, this is even done straight from memory.
We have a great blog post that describes when you’d want to use a primary index.
Take a look and let us know if you have any additional questions.
Ian McCloy (Couchbase Principal Product Manager)