Indexes​

An index is a data structure used internally by a database to speed up filtering and sorting operations. Most commonly, indexes are declared within object type declarations and reference a particular property; this will speed up any query that references that property in a filter or order by clause.

While improving query performance, indexes also increase disk and memory usage and slow down insertions and updates. Creating too many indexes may be detrimental; only index properties you often filter or order by.

Index on a property​

Below, we are referencing the User.name property with the dot notation shorthand: .name.

type User {
  required property name -> str;
  index on (.name);
}

By indexing on User.name, queries that filter by the name property will be faster, as the database can lookup a name in the index instead of scanning through all Users sequentially.

Index on an expression​

Indexes may be defined using an arbitrary singleton expression that references multiple properties of the enclosing object type.

A singleton expression is an expression that’s guaranteed to return at most one one element. As such, you can’t index on a multi property.

type User {
  required property first_name -> str;
  required property last_name -> str;
  index on (str_lower(.firstname + ' ' + .lastname));
}

Index on multiple properties​

A composite index is an index that references multiple properties. This will speed up queries that filter or sort on both properties. In EdgeDB, this is accomplished by indexing on a tuple of properties.

type User {
  required property name -> str;
  required property email -> str;
  index on ((.name, .email));
}

Index on a link property​

Link properties can also be indexed.

abstract link friendship {
  property strength -> float64;
  index on (__subject__@strength);
}
type User {
  multi link friends extending friendship -> User;
}

Annotate an index​

Indexes can be augmented with annotations.

type User {
  property name -> str;
  index on (.name) {
    annotation description := 'Indexing all users by name.';
  };
}

Foreign and primary keys

In SQL databases, indexes are commonly used to index primary keys and foreign keys. In EdgeDB, these fields are automatically indexed; there’s no need to manually declare them. Moreover, any property with an exclusive constraint is also automatically indexed.

︙