Understanding ino:explain with compound indexes

, ,
1

Hi, I’m currently trying to optimize indexes for a Tamino 4.2 database. I want to introduce some compount indexes in order to replace some indexes based on single fields.

Lets say, I have a docType where documents are identified by @class and @key (in the root tag) and all filters use @class and some additionally @key.

Currently there will be two individual indexes on @class and @key, but it seems to make sense to use a compound index here (maybe even with unique constraint).
I’ve tried this and hoped that ino:explain for my XPath expression would differ significently, but I don’t see much. On default level I see that preselection is true with both schema definition. On the more detailled levels this remains and I currently have no clue how to interpret the output for compound indexes (the docs only cover a single component index), because it almost looks the same as with the old index definitions…

Apart from this the xop:preselectable values are not clear to me. They are always false in my tests… :shock:
What’s the criteria for them to switch to true? Should I bother about “true” values here at all?

Thanks in advance
Juergen :roll:

2

When queries always use the class attribute, and sometimes additionally the key attribute, define a compound index that has class in the first and key in the second position. It will be usable for queries like

docType[@class = "classvalue"]

and

docType[@class = "classvalue" and @key = "keyvalue"]

i.e. for any query that goes for a prefix of the compound value.

Regardless of one compound index or two individual indexes, your ino:explain output for these queries should, at the top level, indicate

ino:preselection="TRUE"

because some predicates are evaluated at index processing time, and

ino:postprocessing="FALSE"

because these queries can be evaluated index-only.

For inspecting the index usage in detail, refer to the “output tree for index processor” section of the explain output. This will contain those parts of the query that have qualified for index-based evaluation, i.e. they are processed before evaluating predicates on instance level.

With individual indexes, for the latter query, you should see your value comparisons represented by xop:eq, xop:nametest, and xop:literal elements. The comparisons then are combined in a xop:and.

With the compound index, instead of the above you will find a xop:indexscan element, which combines two xop:indexcomponent elements containing your search values in xop:literalsequence elements. This represents the process of building a compound search value which is used for querying the index.

The “output tree for index processor” section never contains any xop:preselectable attributes. You may however find them in the “output tree from optimization” section. Any subtree flagged xop:preselectable=“FALSE” does not qualify for index processing.

Hope this helps.