Functional Index vs Secondary Index Priority

Couchbase Server
1

Hi All,

I’m using Couchbase 6.5.1 and have below query:

SELECT *
FROM myBucket table2
LEFT
JOIN myBucket table1 ON
TRIM(table1.accountno) = table2.chdrnum
AND table1.SRC_TABLE_NAME = ‘table1’
AND table1.code = ‘XX’
AND table1.type_ = ‘PS’
WHERE table2.SRC_TABLE_NAME = ‘table2’
AND table2.validflag <> ‘2’
AND TO_NUMBER(table2.aprvdate) BETWEEN 20210107 AND 20210707
AND table2.qadecision IN [‘’,’ ',‘P’]
ORDER BY TO_NUMBER(table2.aprvdate) DESC
LIMIT 10
OFFSET 0

When I try to advise this query, I’ve got below result:

CREATE INDEX idx-myBucket-table2-05-PARTITION ON myBucket(to_number(aprvdate) DESC,qadecision) WHERE ((SRC_TABLE_NAME = ‘table2’) and (not (validflag = ‘2’)))
CREATE INDEX idx-myBucket-table1-03 ON myBucket(accountno,type_,code) WHERE (SRC_TABLE_NAME = ‘table1’)

While I have another index :

CREATE INDEX idx-myBucket-table1-04 ON myBucket(trim(accountno),type_,code) WHERE (((SRC_TABLE_NAME = “table1”) and (code = “XX”)) and (type_ = “PS”))

is not being called.

My Question:
Why my functional index idx-myBucket-table1-04 is not being called?

2

May be older version

https://index-advisor.couchbase.com/indexadvisor/#1
It shows functional index. If use EXPLAIN it may use that index

SELECT *
FROM myBucket table2
LEFT
JOIN myBucket table1 ON
TRIM(table1.accountno) = table2.chdrnum
AND table1.SRC_TABLE_NAME = "table1"
AND table1.code = "XX"
AND table1.type_ = "PS"
WHERE table2.SRC_TABLE_NAME = "table2"
AND table2.validflag <> "2"
AND TO_NUMBER(table2.aprvdate) BETWEEN 20210107 AND 20210707
AND table2.qadecision IN [""," ","P"]
ORDER BY TO_NUMBER(table2.aprvdate) DESC
LIMIT 10
OFFSET 0
3

Hi @vsr1 ,

When I try using EXPLAIN, it also show using index without trim.
the query become slower if it use the index without trim

4

u can drop that index or USE INDEX hint