Light
Dark
System
v4latest
v5dev
v4latest
v3
v2
v1

Indexes

An index is a data structure used internally to speed up filtering, ordering, and grouping operations. Indexes help accomplish this in two key ways:

  • They are pre-sorted which saves time on costly sort operations on rows.

  • They can be used by the query planner to filter out irrelevant rows.

The Postgres query planner decides when to use indexes for a query. In some cases — for example, when tables are small and it would be faster to scan the whole table than to use an index — an applicable index may be ignored.

For more information on how it does this, read the Postgres query planner documentation.

Most commonly, indexes are declared within object type declarations and reference a particular property. The index can be used to speed up queries which reference that property in a filter, order by, or group 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, order, or group by.

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

Copy
type User {
  required name: str;
  index on (.name);
}

By indexing on User.name, the query planner will have access to that index for use when planning queries containing the property in a filter, order, or group by. This may result in better performance in these queries as the database can look up a name in the index instead of scanning through all User objects sequentially, although whether or not to use the index is ultimately up to the Postgres query planner.

To see if an index can help your query, try adding the analyze keyword before a query with an index compared to one without.

Even if your database is too small now to benefit from an index, it may benefit from one as it continues to grow.

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 element. As such, you can’t index on a multi property.

Copy
type User {
  required first_name: str;
  required last_name: str;
  index on (str_lower(.firstname + ' ' + .lastname));
}

A composite index is an index that references multiple properties. This can speed up queries that filter, order, or group on both properties.

An index on multiple properties may also be used in queries where only a single property in the index is filtered, ordered, or grouped by. It is best to have the properties most likely to be used in this way listed first when you create the index on multiple properties.

Read the Postgres documentation on multicolumn indexes to learn more about how the query planner uses these indexes.

In EdgeDB, this index is created by indexing on a tuple of properties.

Copy
type User {
  required name: str;
  required email: str;
  index on ((.name, .email));
}

EdgeDB exposes Postgres indexes that you can use in your schemas. These are exposed through the pg module.

  • pg::hash- Index based on a 32-bit hash derived from the indexed value

  • pg::btree- B-tree index can be used to retrieve data in sorted order

  • pg::gin- GIN is an “inverted index” appropriate for data values that contain multiple elements, such as arrays and JSON

  • pg::gist- GIST index can be used to optimize searches involving ranges

  • pg::spgist- SP-GIST index can be used to optimize searches involving ranges and strings

  • pg::brin- BRIN (Block Range INdex) index works with summaries about the values stored in consecutive physical block ranges in the database

You can use them like this:

Copy
type User {
  required name: str;
  index pg::spgist on (.name);
};

Indexes can be augmented with annotations.

Copy
type User {
  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. EdgeDB’s analog to SQL’s primary key is the id field that gets automatically created for each object, while a link in EdgeDB is the analog to SQL’s foreign key. Both of these are automatically indexed. Moreover, any property with an exclusive constraint is also automatically indexed.

Light
Dark
System

We use ChatGPT with additional context from our documentation to answer your questions. Not all answers will be accurate. Please join our Discord if you need more help.