Skip to content

Index-answerable queries planned as full scans: SQL LIKE 'prefix%', Cypher STARTS WITH, and Cypher min()/max() (80-340 ms against 0.3-4 ms for the equivalent rewrite at 1M rows)Β #8666

Description

@tae898

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

  1. 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 the LIKE as a residual filter so the answer does not depend on the collation.
  2. Cypher: the same for STARTS WITH, which the native planner already turns into NodeIndexRangeScan when it is written as >= and <.
  3. Cypher: min(n.p) and max(n.p) over one indexed property, with no grouping key and no filter, read from the end of the index, as SQL's MIN FROM INDEX / MAX FROM INDEX do. With the default NULL_STRATEGY SKIP the index holds no nulls, which is what min / max ignore 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

  1. self-assigned this
    on Sep 29, 2026
  2. added
    in progressThe team is actively working on this issue
    on Sep 29, 2026
  3. added this to the 26.10.1 milestone on Sep 29, 2026
  4. added 3 commits that reference this issue on Sep 29, 2026
  5. removed
    in progressThe team is actively working on this issue
    on Sep 29, 2026
  6. lvca commented on Sep 29, 2026

    @lvca
    Member

    Fixed in #8669 (26.10.1).

    • SQL LIKE 'abc%' and Cypher STARTS WITH 'abc' now use an ordered index on the property (a range from abc up to abd, then the original condition is still checked). Case insensitive indexes are left out so no rows are missed.
    • Cypher MATCH (n:L) RETURN min(n.p) / max(n.p) now reads one end of the index, like SQL MIN/MAX FROM INDEX, when the index is on that label and property alone and holds no nulls.
    • Anything else keeps the previous behaviour, so results are unchanged, only faster.

    ❀️ 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.

  7. tae898 commented on Sep 29, 2026

    @tae898
    ContributorAuthor

    Verified with the issue's PrefixAndMin.java on upstream main at aeb3a745f6 (it carries #8669 through 5991331fc0), against b8ab990821, the build the issue was measured on. Same data, 1,000,000 vertices, p50 of 7 runs, Temurin 25 with compact headers / Temurin 21:

    query before (b8ab990821) after (aeb3a745f6) plan after
    SQL s LIKE 'abc%' 81.58 / 88.91 ms 8.54 / 10.50 ms FETCH FROM INDEX D[s]
    SQL s >= 'abc' AND s < 'abd' 3.92 / 4.34 ms 3.45 / 4.03 ms FETCH FROM INDEX D[s]
    Cypher STARTS WITH 'abc' 305.84 / 311.83 ms 3.65 / 3.59 ms NodeIndexRangeScan
    Cypher min(d.x) 340.54 / 337.49 ms 0.21 / 0.20 ms MIN FROM INDEX D[x]
    Cypher max(d.x) 322.06 / 334.68 ms 0.41 / 0.43 ms MAX FROM INDEX D[x]

    Answers identical (216, 1, 9999998). Thanks.

  8. added theissue type on Sep 29, 2026
  9. added a commit that references this issue on Sep 30, 2026
  10. added 3 commits that reference this issue on Oct 6, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

engineCore engine: document/schema APIopencypherperformanceseverity:minorCosmetic, documentation, edge case, or a workaround existssql

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions