Repository navigation
Description
Three questions that an index answers are planned as full scans when asked the usual way, and are fast when rewritten into a form the planner already serves from the same index: a prefix match in SQL (LIKE 'abc%') and in Cypher (STARTS WITH), and min() / max() in Cypher. At 1,000,000 rows each costs a full scan (80 to 340 ms) against 0.3 to 4 ms for the rewrite, and the answers are identical.
Engine 26.10.1-SNAPSHOT b8ab990821, embedded, laptop NVMe. One vertex type D with 1,000,000 vertices: x INTEGER uniform in [0, 10,000,000) and s an 8-character hex string, each with a NOTUNIQUE index. p50 of 7 runs after 2 warm-ups; the plan column is the first operator of EXPLAIN. Temurin 25 with compact headers is the run that printed the plans; Temurin 21 is the run before it, same data, without the plan column.
| query | Temurin 25 | Temurin 21 | answer | plan |
|---|---|---|---|---|
SQL SELECT count(*) FROM D WHERE s LIKE 'abc%' |
81.58 ms | 88.91 ms | 216 | FETCH FROM TYPE D WITH FILTER (parallel) |
SQL ... WHERE s >= 'abc' AND s < 'abd' |
3.92 ms | 4.34 ms | 216 | FETCH FROM INDEX D[s] |
Cypher MATCH (d:D) WHERE d.s STARTS WITH 'abc' RETURN count(*) |
305.84 ms | 311.83 ms | 216 | NodeByLabelScan(d:D) [filter: d.s STARTS WITH 'abc'] |
Cypher ... WHERE d.s >= 'abc' AND d.s < 'abd' ... |
3.69 ms | 3.70 ms | 216 | NodeIndexRangeScan(d:D) [index=D[s], s >= abc < abd] |
SQL SELECT min(x) FROM D |
0.29 ms | 0.28 ms | 1 | MIN FROM INDEX D[x] |
Cypher MATCH (d:D) RETURN min(d.x) |
340.54 ms | 337.49 ms | 1 | NodeByLabelScan(d:D) |
Cypher MATCH (d:D) RETURN max(d.x) |
322.06 ms | 334.68 ms | 9999998 | NodeByLabelScan(d:D) |
Cypher MATCH (d:D) RETURN d.x ORDER BY d.x LIMIT 1 |
0.30 ms | 0.28 ms | 1 | NodeIndexRangeScan(d:D) [index=D[x], x] [index order, then null keys] |
Asked
- SQL: plan
LIKE 'literal%'(a literal or parameter prefix with no other wildcard) as a range on the index, from the prefix to the prefix with its last character incremented, keeping theLIKEas a residual filter so the answer does not depend on the collation. - Cypher: the same for
STARTS WITH, which the native planner already turns intoNodeIndexRangeScanwhen it is written as>=and<. - Cypher:
min(n.p)andmax(n.p)over one indexed property, with no grouping key and no filter, read from the end of the index, as SQL'sMIN FROM INDEX/MAX FROM INDEXdo. With the defaultNULL_STRATEGY SKIPthe index holds no nulls, which is whatmin/maxignore anyway.
Duplicates checked: none found for any of the three (#5321 and #6577 touch prefix lookups and LIKE in other ways). Found while probing which index-answerable queries the planners serve; the ascending ORDER BY case from the same probe is #8664. The repro is PrefixAndMin.java below; java PrefixAndMin <dbDir> 1000000.
PrefixAndMin.java
import com.arcadedb.database.*;
import com.arcadedb.query.sql.executor.*;
import java.util.*;
/**
* Three questions an index answers, asked two ways each: the way a user writes them, and a rewrite that the planner
* serves from the same index. N vertices of type D, x INTEGER uniform in [0, 10,000,000) and s an 8-character hex
* string, each with a NOTUNIQUE index. p50 of 7 runs after 2 warm-ups, the answer, and the plan's first operators.
*
* java PrefixAndMin <dbDir> <n>
*/
public class PrefixAndMin {
public static void main(String[] a) {
final String dir = a[0];
final int n = Integer.parseInt(a[1]);
System.out.println("\nRESULT engine " + com.arcadedb.Constants.getRawVersion() + " " + com.arcadedb.Constants.getBuildNumber()
+ ", java " + System.getProperty("java.version") + ", n=" + n);
final DatabaseFactory f = new DatabaseFactory(dir);
if (f.exists())
f.open().drop();
final Database db = f.create();
db.command("sql", "CREATE VERTEX TYPE D");
db.command("sql", "CREATE PROPERTY D.x INTEGER");
db.command("sql", "CREATE PROPERTY D.s STRING");
final Random rnd = new Random(11);
db.begin();
for (int i = 0; i < n; i++) {
db.newVertex("D").set("x", rnd.nextInt(10_000_000)).set("s", String.format("%08x", rnd.nextInt())).save();
if ((i + 1) % 20_000 == 0) {
db.commit();
db.begin();
}
}
db.commit();
db.command("sql", "CREATE INDEX ON D (x) NOTUNIQUE");
db.command("sql", "CREATE INDEX ON D (s) NOTUNIQUE");
final String[][] qs = {
{ "sql", "SELECT count(*) AS n FROM D WHERE s LIKE 'abc%'" },
{ "sql", "SELECT count(*) AS n FROM D WHERE s >= 'abc' AND s < 'abd'" },
{ "opencypher", "MATCH (d:D) WHERE d.s STARTS WITH 'abc' RETURN count(*) AS n" },
{ "opencypher", "MATCH (d:D) WHERE d.s >= 'abc' AND d.s < 'abd' RETURN count(*) AS n" },
{ "sql", "SELECT min(x) AS v FROM D" },
{ "opencypher", "MATCH (d:D) RETURN min(d.x) AS v" },
{ "opencypher", "MATCH (d:D) RETURN max(d.x) AS v" },
{ "opencypher", "MATCH (d:D) RETURN d.x AS v ORDER BY d.x LIMIT 1" },
};
for (String[] q : qs) {
final double[] ms = new double[7];
Object v = null;
for (int it = -2; it < 7; it++) {
final long t0 = System.nanoTime();
try (ResultSet rs = db.query(q[0], q[1])) {
final Result r = rs.next();
v = r.getProperty(r.getPropertyNames().iterator().next());
}
if (it >= 0)
ms[it] = (System.nanoTime() - t0) / 1e6;
}
Arrays.sort(ms);
System.out.printf("%nRESULT %-10s %-72s p50 %8.2f ms -> %-8s | %s%n", q[0], q[1], ms[3], v, plan(db, q[0], q[1]));
}
db.close();
}
static String plan(Database db, String lang, String text) {
try (ResultSet rs = db.query(lang, "EXPLAIN " + text)) {
final Object plan = rs.next().getProperty("executionPlanAsString");
final String p = String.valueOf(plan);
for (String line : p.split("\n")) {
final String t = line.trim();
if (t.startsWith("+ FETCH") || t.startsWith("+ MIN") || t.startsWith("+ MAX") || t.startsWith("+ CALCULATE") || t.contains("Scan")
|| t.contains("Seek"))
return (t.startsWith("+ ") ? t.substring(2) : t).replaceAll("\\s+", " ");
}
return "?";
} catch (Exception e) {
return "no plan: " + e.getClass().getSimpleName();
}
}
}
Activity
fix(#8666): no STARTS WITH bounds on a case-insensitive Cypher index;β¦
test(#8666): unit tests for the prefix helpers, a non-BMP min/max keyβ¦
Merge pull request #8669 from ArcadeData/fix/8666-prefix-like-min-max
lvca commentedon Sep 29, 2026
Fixed in #8669 (26.10.1).
LIKE 'abc%'and CypherSTARTS WITH 'abc'now use an ordered index on the property (a range fromabcup toabd, then the original condition is still checked). Case insensitive indexes are left out so no rows are missed.MATCH (n:L) RETURN min(n.p)/max(n.p)now reads one end of the index, like SQLMIN/MAX FROM INDEX, when the index is on that label and property alone and holds no nulls.β€οΈ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.
tae898 commentedon Sep 29, 2026
Verified with the issue's
PrefixAndMin.javaon upstream main ataeb3a745f6(it carries #8669 through5991331fc0), againstb8ab990821, the build the issue was measured on. Same data, 1,000,000 vertices, p50 of 7 runs, Temurin 25 with compact headers / Temurin 21:b8ab990821)aeb3a745f6)s LIKE 'abc%'FETCH FROM INDEX D[s]s >= 'abc' AND s < 'abd'FETCH FROM INDEX D[s]STARTS WITH 'abc'NodeIndexRangeScanmin(d.x)MIN FROM INDEX D[x]max(d.x)MAX FROM INDEX D[x]Answers identical (216, 1, 9999998). Thanks.
fix(ArcadeData#8666): serve LIKE 'prefix%', STARTS WITH and Cypher miβ¦
fix(#8666): serve LIKE 'prefix%', STARTS WITH and Cypher min()/max() β¦
fix(#8666): no STARTS WITH bounds on a case-insensitive Cypher index;β¦
test(#8666): unit tests for the prefix helpers, a non-BMP min/max keyβ¦