How much can I trust Index Advisor?

Hi, I have a very unusual use case and I’m wondering if Couchbase is the solution, especially because of the Index Advisor feature.

We’re working on a low-code platform that lets users create applications the issue we have is that since all data structure is user defined and users can query their data in arbitrary ways it’s very hard to know which fields should get indexed in advance.

So when I saw the Index Advisor feature I thought maybe this would do the trick, if I feed user queries to the Index Advisor I can then create indexes from that.

Knowing this, how reliable is the Index Advisor ? Can I blindly trust it’s recommendations and dynamically create indexes from user queries or do I need to do something else ?

Index advisor is based on query predicates. If query predicates constantly changing it may not work.

CB 7.1.3+

Every collection create index with INCLUDE MISSING attribute CREATE INDEX | Couchbase Docs
It will service any query (it almost like primary index, in addition query predicate reference index keys those will be passed to indexer), For SLA specific queries create specific index

ex:
CREATE INDEX idx_airport_include ON airport(district INCLUDE MISSING, name);

Query WHERE district = “xxx” , it will pass to indexer efficient
Query WHERE name = “xxx” , still pass name to indexer. Indexer might have to do more work because it needs to look second key . One need SLA create name as leading key
Query WHERE zipcode = 99999, still uses index (nothing can be passed to indexer) like primary index.
No WHERE clause only projects district or name or both still can use as covering index
No WHERE clause projects other than index keys, uses index and Fetches documents.

You can trust index advisor to recommend the indexes needed for a specific query.

But your question is really about indexing fields for a database. Any database. And the trade-off between executing a query without (all the matching) indexes, or creating all the matching indexes and then executing the query. The short answer is that it’s more efficient to execute a query ONE time without all the matching indexes, than to create all the matching indexes and then execute the query ONE time. This is why DBAs exist. To determine which indexes are needed for known queries and also for unknown - but likely queries. If it was an efficient practice to - for every query, generate the indexes, then execute the query - then databases would do that. But they don’t. Generating an index takes resources in time and storage.

1 Like

A given query will be executed multiple time over the lifetime of the application, we let users create “views” in which they define which fields they want to retrieve and under which condition, so the idea was to use this view definition to generate a Base query and use that base query to generate indexes.

Yes. So it’s definitely possible. You’ll need to figure out if it suits your usage.
Keep in mind your “query-over-a-base-query” will be treated as a query, and whatever indexes were made for the “base-query” may or may not be useful for the query-over-a-base-query.

2 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.