Index selection changed/bug

[CatCodingHelper]

Hi,

When updating our code from ArcadeDB 25.3.2 to 25.4.1 performance was severely degraded. After some debugging it seems to be a different index selection method.

I have created a test case below that replicates the problem. We have three properties, each with a non unique full text index, and a unique lsm index covering the three. We need to be able to do partial matches on the individual properties, but also search for an exact match of the three. We have lots of data stored so need good indexes for search performance.

If you run the case on each version you get the different results listed below. Is this an expected change, or a bug, or should we use a different method now?

Thanks!

`package arcadedb_issues;

import java.io.File; import java.io.IOException; import java.util.List;

import com.arcadedb.database.BasicDatabase; import com.arcadedb.database.DatabaseFactory; import com.arcadedb.database.MutableDocument; import com.arcadedb.query.sql.executor.ExecutionPlan; import com.arcadedb.query.sql.executor.ExecutionStep; import com.arcadedb.query.sql.executor.FilterStep; import com.arcadedb.query.sql.parser.ExplainResultSet; import com.arcadedb.schema.DocumentType; import com.arcadedb.schema.Schema; import com.arcadedb.schema.Schema.INDEX_TYPE;

public class MultiFieldIndexIssue { private MultiFieldIndexIssue() {}

public static void main(final String[] args) throws IOException {
	final File tmp = File.createTempFile("ArcadeTmp", "");
	tmp.delete();
	System.out.println("Tmp Database: " + tmp);
	
	try(final DatabaseFactory fact = new DatabaseFactory(tmp.toString())){
		try(final BasicDatabase local = fact.create()){
			local.begin();
			
			final Schema schema = local.getSchema();
			
			// Create type
			final DocumentType tc = schema.createDocumentType("example");
			
			// Create props
			tc.createProperty("p1", String.class);
			tc.createProperty("p2", String.class);
			tc.createProperty("p3", String.class);
			
			// Index props
			tc.createTypeIndex(INDEX_TYPE.FULL_TEXT, false, "p1");
			tc.createTypeIndex(INDEX_TYPE.FULL_TEXT, false, "p2");
			tc.createTypeIndex(INDEX_TYPE.FULL_TEXT, false, "p3");
			tc.createTypeIndex(INDEX_TYPE.LSM_TREE, true, "p1", "p2", "p3");
			
			// Add dummy data
			final String[] perm = { "a", "b", "c", "d", "e", "f", "g", "h" };
			for(final String l1 : perm) {
				for(final String l2 : perm) {
					for(final String l3 : perm) {
						final MutableDocument doc = local.newDocument("example");
						doc.set("p1", l1);
						doc.set("p2", l2);
						doc.set("p3", l3);
						doc.save();
					}
				}
			}
			
			local.commit();
			local.begin();
			
			final ExplainResultSet rs = (ExplainResultSet)local.command("sql", "explain select * from example where p1=? and p2=? and p3=?", "e", "b", "h");
			System.out.println();
			
			final ExecutionPlan plan = rs.getExecutionPlan().get();
			System.out.println(plan.prettyPrint(0, 3));
			
			final List<ExecutionStep> steps = plan.getSteps();
			for(final ExecutionStep step : steps) {
				if(step instanceof FilterStep) {
					throw new IllegalStateException("Execution contains filter, not using index");
				}
			}
		}
	}
	
}

} `

Working output:

• FETCH FROM INDEX example[p1,p2,p3] p1 = ? AND p2 = ? AND p3 = ?
• EXTRACT VALUE FROM INDEX ENTRY filtering buckets [1]
• FILTER ITEMS BY TYPE example
• CALCULATE PROJECTIONS *

Broken output (with exception):

• FETCH FROM INDEX example[p1] p1 = ?
• EXTRACT VALUE FROM INDEX ENTRY filtering buckets [1]
• FILTER ITEMS WHERE p2 = ? AND p3 = ?
• FILTER ITEMS BY TYPE example
• CALCULATE PROJECTIONS *

[gramian]

IS the problem only occurring if all four indexes are used, or also if you only use the three non-unique indexes or only the unique composite index?

[CatCodingHelper]

Thanks for looking:

• if I take away the 3 single, it uses the composite ok
• if I take away the composite, it still uses one of the non unique (as if composite was still there)

Unfortunately I have use cases that need all 4.

[gramian]

I understand, the idea was if the composite unique index is the problem, then adding a fourth property being a hash of the other three and index that instead could be a workaround for now.

[CatCodingHelper]

ah, Great idea! I get you - will give that a go tonight - Thanks!