ORDER BY Clause is causing performance degradation

Couchbase Server
,
1

select t.id from table as t where t.doc_type = ‘item’
AND t.data.commercial_value is not null
order by t.data.commercial_value desc limit 500 offset 0

This query takes ~2 secs by using the below 2 indexes. But if we remove the Order clause it’s executed in ~110 ms

select t.id from table as t where t.doc_type = ‘item’
limit 500 offset 0

Indexes:

CREATE INDEX idx_doc_type ON table(doc_type)

CREATE INDEX idx_comm_val ON table((data.commercial_value) DESC)

Is there a way we can optimize it further, any issues with our indexing approach ?

Any help would be appreciated.

2

CREATE INDEX idx_comm_val ON table ((data . commercial_value DESC, id) WHERE doc_type = “item”;

https://index-advisor.couchbase.com/indexadvisor/#1

3

Hi @vsr1 … Thanks for that material. Will go through it.

Can you pls clarify couple of points here :

  • select clause selects almost 30 fields from the documents, do we need to list all of them in the same order instead of id (from the index that you have created)
  • WHERE clause is also very dynamic, I mean it can have multiple AND conditions. In this case, do we need to create individual indexes on field, just like above ??
4

Above material covers those info.