GROUP BY full scan: BucketIterator re-resolves each record through 2 extra page lookups (from #8260)

@lvca

Split out of #8260, item 1 ("BucketIterator.fetchNext() looks each record up two more times").

What #8260 found

For an in-page record, BucketIterator.fetchNext() (engine/src/main/java/com/arcadedb/engine/BucketIterator.java) already holds currentPage, the record's recordPositionInPage and its recordSize marker, but still resolves the record through:

bucket.existsRecord(rid) - re-reads the same marker through a fresh page-manager lookup of the same page, under its own per-file permission check.
database.lookupByRID(rid, false) - builds a lazy shell (no existence check of its own).
On first property access, ImmutableDocument.checkForLazyLoading() re-fetches the content via bucket.getRecord(rid), a third independent page-manager lookup, this time under checkPermissionsOnFile again too.

The reporter measured removing just the first of these (their "prototype A") at -72% on a bare iterateType scan, -17% to -18% once a property is read, and -8% to -14% on the full SQL GROUP BY/scan-count paths (Temurin 21, TPC-H-shaped LineItem, full numbers and repro in #8260).

Why this is a separate issue, not part of #8228/#8260's first PR

I prototyped the straightforward fix - existsRecord() removed, lookupByRID(rid, false) kept as-is - and it passes the full engine test suite (2324 tests, com.arcadedb.query.sql.parser.** + com.arcadedb.query.sql.executor.**, plus the targeted event/lazy-loading/corrupted-scan suites). Its correctness cost is real but negligible: TransactionContext.getPage() does not cache per-transaction under READ_COMMITTED, so existsRecord()'s fresh read can occasionally observe a delete that currentPage's already-read marker predates, and removing it narrows that detection to whenever the record is eventually lazy-loaded instead. In practice this does not widen the window in any way that matters: the dominant, unbounded gap - from shell creation to whenever a caller actually touches a property - is untouched either way, existsRecord() only ever covered the sub-microsecond gap between itself and lookupByRID().

I then tried to close that gap for real - construct the record straight from currentPage's bytes (recordSize[0] > 0 case), the way LocalBucket.scan() and this same method's PLACEHOLDER branch already do, firing AfterRecordReadListener/BeforeRecordReadListener immediately instead of deferring them to first property access. This is where it gets interesting: ImmutableVertex.modify() (and ImmutableDocument.modify(), for a record not yet tracked via getPageToModify()) reload the record UNCONDITIONALLY on first modify(), regardless of whether its buffer is already populated. So a record built eagerly in the scan still pays a second, mandatory fetch and fires the read listeners a second time on the ordinary "scan, then modify" pattern - which is about as common as it gets in a graph database - and broke DatabaseEventsTest#afterRead / #beforeRead (both assert the listener fires exactly once).

Closing the race properly therefore means also changing modify()'s unconditional-reload invariant (skip the reload when the buffer is already fresh and untouched), which is a separate, correctness-critical change to a different code path, deserving its own review and its own regression tests rather than riding along on a scan-performance PR.

Suggested follow-up

Ship the safe part (existsRecord() removed from the in-page branch, everything else unchanged) as a small, low-risk PR on its own.
Separately, look at whether ImmutableVertex.modify() / ImmutableDocument.modify() can skip their unconditional reload when the record's buffer is already populated and not concurrently invalidated - only after that lands does eager construction in BucketIterator (closing the race and dropping the third lookup too) become safe to attempt again.

@lvca

fix(ArcadeData#8260): GROUP BY no longer rebuilds the default project…

One more data point for step 2, from checking whether the SQL parallel scan would help. The same 2,000,000 rows went into a default type and into a type created with BUCKETS 4, so arcadedb.queryParallelScan engages (PROFILE shows FETCH FROM TYPE Four (parallel), and the ArcadeDB-ParallelScanProducer threads run). 4 cores, snapshot 5b040a438e, two rounds each:

	1 bucket	4 buckets (parallel scan)
SQL `GROUP BY l_shipmode, count(*)`, Temurin 21	3,081 / 3,162 ms	3,969 / 4,017 ms
same, Temurin 25 + COH	2,700 / 2,772 ms	3,654 / 3,440 ms
SQL `count(*) WHERE l_quantity >= 0`, Temurin 21	2,508 / 2,467 ms	2,631 / 2,489 ms
same, Temurin 25 + COH	2,196 / 2,231 ms	2,295 / 2,345 ms
Java `iterateType` loop (never parallel), Temurin 21	2,243 / 2,024 ms	2,119 / 2,044 ms

So the parallel scan makes this GROUP BY 24% to 35% slower and the scan count no faster. A JFR profile of the 4-bucket GROUP BY, split by thread, shows why. The producers do the cheap half: fetchNext, existsRecord and lookupByRID(rid, false). The consumer thread still does the expensive half: checkForLazyLoading() into LocalBucket.getRecord (12.9% of all samples, 0% on the producers), the deserialization, the projection and the aggregation. The producers hand over lazy shells, so the content load that the parallel scan exists to spread stays on one thread. On top of that, every record crosses the LinkedBlockingQueue one at a time: poll is 8.5% of all samples, most of it waking a producer through signalNotFull().

Two things follow, offered only as input to how you sequence this. Step 2 (content loaded in the iterator) is also what would let the parallel scan spread the load at all. And the per-record handoff could move in batches, a list per offer, which does not depend on the modify() question. I have not prototyped either.

BucketParallelScan.java

import com.arcadedb.GlobalConfiguration;
import com.arcadedb.database.Database;
import com.arcadedb.database.DatabaseFactory;
import com.arcadedb.database.Document;
import com.arcadedb.database.MutableDocument;
import com.arcadedb.database.Record;
import com.arcadedb.query.sql.executor.Result;
import com.arcadedb.query.sql.executor.ResultSet;

import java.util.*;

/**
 * The SQL parallel scan (arcadedb.queryParallelScan, on by default) runs only on a type with at least
 * arcadedb.queryParallelScanMinBuckets (2) buckets, and arcadedb.typeDefaultBuckets is 1. Same rows in a default
 * type and in a 4-bucket type; the same GROUP BY, scan count and Java loop on each. Answers checked against a
 * Java-API count.
 *
 *   java -cp 'lib/*:cls' BucketParallelScan <dbPath> <rows>
 */
public class BucketParallelScan {
  static final String[] MODES = { "AIR", "FOB", "MAIL", "RAIL", "REG AIR", "SHIP", "TRUCK" };

  public static void main(String[] a) throws Exception {
    final int n = Integer.parseInt(a[1]);
    try (DatabaseFactory f = new DatabaseFactory(a[0])) {
      if (f.exists()) f.open().drop();
      final Database db = f.create();
      System.out.printf("cores=%d queryParallelScan=%s minBuckets=%s typeDefaultBuckets=%s engine=%s%n",
          Runtime.getRuntime().availableProcessors(), db.getConfiguration().getValueAsBoolean(GlobalConfiguration.QUERY_PARALLEL_SCAN),
          db.getConfiguration().getValueAsInteger(GlobalConfiguration.QUERY_PARALLEL_SCAN_MIN_BUCKETS),
          db.getConfiguration().getValueAsInteger(GlobalConfiguration.TYPE_DEFAULT_BUCKETS), com.arcadedb.Constants.getVersion());
      db.command("sql", "CREATE DOCUMENT TYPE One");
      db.command("sql", "CREATE DOCUMENT TYPE Four BUCKETS 4");
      for (String t : new String[] { "One", "Four" }) {
        db.command("sql", "CREATE PROPERTY " + t + ".l_quantity DOUBLE");
        db.command("sql", "CREATE PROPERTY " + t + ".l_shipmode STRING");
        db.command("sql", "CREATE PROPERTY " + t + ".l_extendedprice DOUBLE");
      }
      System.out.printf("buckets: One=%d Four=%d%n", db.getSchema().getType("One").getBuckets(false).size(),
          db.getSchema().getType("Four").getBuckets(false).size());
      final Random r = new Random(1);
      db.begin();
      for (int i = 0; i < n; i++) {
        final String mode = MODES[r.nextInt(MODES.length)];
        final double q = 1 + r.nextInt(50), p = 900 + r.nextDouble() * 100_000;
        for (String t : new String[] { "One", "Four" }) {
          final MutableDocument d = db.newDocument(t);
          d.set("l_quantity", q); d.set("l_shipmode", mode); d.set("l_extendedprice", p);
          d.save();
        }
        if ((i + 1) % 50_000 == 0) { db.commit(); db.begin(); }
      }
      db.commit();
      for (String t : new String[] { "One", "Four", "One", "Four" }) {
        final Map<String, Long> truth = javaCount(db, t);
        time(t, "SQL GROUP BY l_shipmode count(*)", () -> sqlGroup(db, t), truth);
        time(t, "SQL count(*) WHERE l_quantity >= 0", () -> sqlCount(db, t), (long) n);
        time(t, "Java iterateType + getString", () -> javaCount(db, t), truth);
      }
      db.close();
    }
  }

  interface Q { Object run(); }

  static void time(String type, String what, Q q, Object truth) {
    q.run();
    final double[] ms = new double[5]; Object ans = null;
    for (int i = 0; i < 5; i++) { final long s = System.nanoTime(); ans = q.run(); ms[i] = (System.nanoTime() - s) / 1e6; }
    Arrays.sort(ms);
    System.out.printf("RESULT %-4s %-36s median %7.0f ms [%.0f-%.0f]%s%n", type, what, ms[2], ms[0], ms[4], truth.equals(ans) ? " ok" : " ANSWER DIFFERS " + ans);
  }

  static Map<String, Long> sqlGroup(Database db, String t) {
    final Map<String, Long> m = new HashMap<>();
    try (ResultSet rs = db.query("sql", "SELECT l_shipmode, count(*) AS n FROM " + t + " GROUP BY l_shipmode")) {
      while (rs.hasNext()) { final Result x = rs.next(); m.put(x.getProperty("l_shipmode"), ((Number) x.getProperty("n")).longValue()); }
    }
    return m;
  }

  static Long sqlCount(Database db, String t) {
    try (ResultSet rs = db.query("sql", "SELECT count(*) AS n FROM " + t + " WHERE l_quantity >= 0")) {
      return ((Number) rs.next().getProperty("n")).longValue();
    }
  }

  static Map<String, Long> javaCount(Database db, String t) {
    final Map<String, Long> m = new HashMap<>();
    final Iterator<Record> it = db.iterateType(t, false);
    while (it.hasNext()) m.merge(((Document) it.next().asDocument()).getString("l_shipmode"), 1L, Long::sum);
    return m;
  }
}

That's a great idea, moving the checkForLazyLoading() into the parallel fetch so it goes in parallel. Also the LinkedBlockingQueue could be not the most efficient choice for high concurrency. I'll take all this into consideration.

fix(#8265,#8266): silence a PMD false positive on the sentinel identi…

fix(#8265): parallel scan producers must respect loadContent()'s filt…

docs(#8265): note the accepted LIMIT deserialization trade-off in the…

fix(#8265): parallel-scan queue capacity rounds up instead of truncating

fix(#8265): bound parallel-scan batches by bytes, not just row count

docs(#8265): edges are ImmutableDocuments too, so the parallel scan l…

fix(#8265): a non-positive queryParallelScanMaxBatchBytes disables th…

fix(#8265): bound the rows a parallel-scan producer reads per read-lo…

Merge pull request #8311 from ArcadeData/fix/8265-8266-scan-double-lo…

Fixed in #8311, available in 26.10.1.

A full scan no longer looks up each record a second time: it already has the record's page in hand.
The SQL parallel scan (types with 2+ buckets) now reads the records on its worker threads instead of leaving that work to the single thread consuming the results, and hands rows over in batches instead of one at a time.

On 1M rows, GROUP BY went from 408 ms to 314 ms on a 1-bucket type and from 601 ms to 324 ms on a 4-bucket type, so the parallel scan is no longer slower than the plain one. A bare iterateType() loop is about 3x faster.

A new setting, arcadedb.queryParallelScanMaxBatchBytes (default 16MB), caps how much record data one batch of the parallel scan holds in memory. Records hidden by an after-read event are now left out of parallel scan results.

Not included: building records straight from the page during a plain (non-parallel) scan. That needs modify() to stop reloading a record it already has, which deserves its own change.

❤️ 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

@tae898 can't wait to see how much we improved!

perf(ArcadeData#8265,ArcadeData#8266): scans stop re-resolving each r…

CAMPAIGN re-pin checklist: the bucket count re-opens (ArcadeData#8265 …

Measured on #8260 (#8260), together with #8266 since both landed in #8311: on Temurin 25 with compact object headers and on Temurin 21, the bare scan is 2.2x to 2.3x faster and a full-scan GROUP BY 1.34x to 1.36x, and the parallel scan now wins, four buckets about 1.4x faster than one where they were 35% slower before. One question about the default bucket count is there too. Thanks!

Navigation Menu

Repository navigation

GROUP BY full scan: BucketIterator re-resolves each record through 2 extra page lookups (from #8260) #8265

What #8260 found

Why this is a separate issue, not part of #8228/#8260's first PR

Suggested follow-up

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

GROUP BY full scan: BucketIterator re-resolves each record through 2 extra page lookups (from #8260) #8265

Description

What #8260 found

Why this is a separate issue, not part of #8228/#8260's first PR

Suggested follow-up

Activity

tae898 commented on Sep 24, 2026

lvca commented on Sep 24, 2026

lvca commented on Sep 24, 2026

lvca commented on Sep 24, 2026

tae898 commented on Sep 25, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions