Covering Indexes with IntersectScan or UnionScan

SQL++
1

Hi,

I am trying to understand how covering indexes works. As far as I can see, if the query can be resolved by using only 1 index, it will use covering indexes, but as soon as there are multiple indexes involved, even if one of them do act as a covering index, it looks like we are loosing the covering index and we are back to using fetches…

For example, If I have documents with 5 fields, A, B, C, D, E…

I have the following indexes:
index1: index on fields A, B, C
index2: index on field D
index3: index on field E

For a query like:
SELECT A FROM bucket WHERE B = “aValue” ORDER BY C -> this uses index1 as a covering index, which is great

But then, sometimes I have extra filters in the where clause for fields D and/or E

So another query like:
SELECT A FROM bucket WHERE B = “aValue” AND C = “anotherValue” ORDER BY C -> this uses index1 and index 2 but do not show any usage of covering index and therefore do fetches! It should be able still to use index1 as a covering index.

Am I missing something?

I could multiply the number of indexes but this could get out of hand quickly if you have a lot of optional fields that can be mix and matched!

Thanks.

2

Hi @lbertrand, you are correct on both counts. This is the current behavior, and it can be improved.

For now (including Couchbase 4.5), I would recommend that you add a USE INDEX clause if you want to prefer the covering index over the IntersectScan.

Thanks,
Gerald

3

If i do this, it will not avoid the fetch, I guess, as it still need the index 2 or index 3 to filter items so not sure how this will really help…

Base on your response, this improvement to the query engine is not part of 4.5 either… Is it plan for a future release?

4

Yes, USE INDEX will avoid the first fetch, which is the most significant. You can compare the EXPLAIN plans and see for yourself, and then compare the performance.

This improvement is not planned for 4.5.

5

Thanks…

I realise I also could create an index on A, B, C, D, E and always use the covering index in this case! If the base query is fast enough, in this case, because the index will not be able to be used fully with combination of the optional filtering item, for example, I have E not D, it will not be worse than the default case. And I avoid any fetches.

1 Like