Skip to main content

Indexing & Performance

DortDB has a rule-based optimizer and no table statistics, so it will not reorder your joins for you. The two levers you have are secondary indices and optimizer configuration.

Add a secondary index​

createIndex builds an index over one or more expressions of a source:

import { DortDB, MapIndex } from '@dortdb/core';

db.registerSource(['users'], users);

// index on a column
db.createIndex(['users'], ['age'], MapIndex);

// the indexed expression can be any subquery-free expression
db.createIndex(['users'], ['name[0] + age'], MapIndex);

The optimizer picks up matching indices automatically: an index on age turns WHERE age = 35 into an index lookup. Each index class decides which expressions it can serve, so MapIndex applies to equality but not, say, to range comparisons.

Indexed expressions are parsed with the default language. For item sources such as graph nodes, name the identifier that stands for the item:

db.createIndex(['social', 'nodes'], ['x.id'], MapIndex, {
  mainLang: 'cypher',
  fromItemKey: ['x'],
});

Speed up graph traversal​

@dortdb/lang-cypher ships a specialized ConnectionIndex that stores no data of its own. It detects join conditions between nodes and relationships and resolves the connected edges and nodes through the graph adapter:

import { ConnectionIndex } from '@dortdb/lang-cypher';

db.createIndex(['social', 'nodes'], [], ConnectionIndex);

Enable hash joins​

An index class can also accelerate joins over streams that are not themselves indexed. Make the classes available to the executor via hashJoinIndices:

const db = new DortDB({
  mainLang: SQL(),
  optimizer: { rules: defaultRules },
  executor: { hashJoinIndices: [MapIndex] },
});

MapIndex covers equality joins. When no suitable index is available, joins fall back to a nested-loop join.

Configure the optimizer​

The optimizer applies an ordered list of rewrite rules. For tree-shaking it starts empty, so pass defaultRules to get the recommended, pre-ordered set:

import { defaultRules } from '@dortdb/core/optimizer';

new DortDB({ mainLang: SQL(), optimizer: { rules: defaultRules } });

Because order matters and the rules are independent, you can reorder them, use a subset, or drop optimization entirely, and change your mind at runtime:

import { UnnestSubqueries } from '@dortdb/core/optimizer';

db.optimizer.reconfigure({ rules: [UnnestSubqueries] });

What each rule does, in default order and with before/after plans, is in Optimization. To add a rule of your own, see Optimizer Rules.

Experiment in the Showcase

The Showcase demo lets you toggle and reorder individual rules and watch the logical plan change. A quick way to build intuition for what each one does.

When queries are still slow​

Execution is interpreted, synchronous and single-threaded, which is generally competitive on relational and aggregation workloads and slower on deep tree traversal. The shape that hurts most is many joins with no indices, because without statistics the optimizer cannot reorder them: write the joins in a sensible order yourself, and index the attributes you filter and join on. See Limitations for why these constraints exist.