New Query Optimizer in CB 6.5?

SQL++
1

Hi,

After upgrading to Couchbase 6.5 some of my queries are very slow. If more than 1 index can be used for the query, couchbase 6.5 uses not always the best possible index, while couchbase 6.0 always found the best one. Query answer times are up to 20x slower now. I can easy fix this with “use index (indexname)”, so not a big problem. But is this a known issue? I read in the release notes about the “cost based optimizer”. But this is not active, so I excpect that everything should work like in the old version?

Thanks, Pascal

2

Will it possible to provide the index definitions (one does better vs slow) and query.

May be following contributing it.
https://docs.couchbase.com/server/6.5/learn/services-and-indexes/indexes/index_pushdowns.html#composite-predicate-with-skip-key-range-scan

3

Good Morning,

Query:

SELECT content.content.id FROM content WHERE content.type=“metadata” AND ANY v IN content.content.tenantIDs SATISFIES v=7 END AND content.content.publishStatus=“published” ORDER by content.content.publishedAt DESC LIMIT 50

Index Definitions:

CREATE INDEX newest ON content(type,(distinct (array v for v in ((self.content).tenantIDs) end)),((self.content).publishStatus),((self.content).publishedAt) DESC,((self.content).id)) WHERE (type = “metadata”) WITH { “defer_build”:true, “nodes”:[ “xxx.xxx.xxx.xxx”,“xxx.xxx.xxx.xxx” ], “num_replica”:1 }

CREATE INDEX newestResNa ON content(type,(distinct (array v for v in ((self.content).tenantIDs) end)),((self.content).resourceName),((self.content).publishStatus),((self.content).publishedAt) DESC,((self.content).id)) WHERE (type = “metadata”) WITH { “nodes”:[ “xxx.xxx.xxx.xxx”,“xxx.xxx.xxx.xxx” ], “num_replica”:1 }

The query uses "newestResNa " instead of “newest”, which makes no sense for me.

Querytime with index newestResNa: >400ms (and some times much slower, up to some seconds)
Querytime with index newest: < 30ms (stable, always fast).

Couchbase 6.0 uses “newest”. We didn’t change anything, just upgraded the database. I can reproduce this. Our staging environmend (CB 6.5) uses the wrong index, prod environment (CB 6.0) uses the correct index. Of course a “USE INDEX (newest)” fixes this and everything is ok. But still, the choosed index makes no sense for me.

A query that makes sense for “newestResNa”-Index is fast, as expected ans uses the correct index.
SELECT content.content.id FROM content WHERE content.type=“metadata” AND ANY v IN content.content.tenantIDs SATISFIES v=7 END AND content.content.resourceName=“articles” AND content.content.publishStatus=“published” ORDER by content.content.publishedAt DESC LIMIT 50

Do I something wrong? Or is this maybe a bug in the new query optimizer? I can open a enterprise ticket about this, but I thought this could be interesting for other users, so I opened a forum thread.

Thanks, Pascal

4

This is side affect of skip range scan example posted previous post link. Will be fixed in next maintenance release. It is not convenient but you can use index hint in meantime. You can open enterprise ticket too.

FYI:
Query1 : newest index able to use first 3 index keys indexscan that resulted in query expose index order avoid sort.
newestResNa index thought it can use 4 index keys (in 6.0 this use to continuous keys 2) pushdwon due to skip scan and decided it is better that resulted in not able to expose index order require sort .

5

I’ll stay with index hint for now. This works perfectly. So after some more testing I don’t see any more blockers to upgrade to Couchbase 6.5. :slight_smile: Thanks for the answers.