Keys and Indexes

Indexes are crucial in improving database performance by allowing faster data retrieval. To understand the significance of indexes in Rell, let's start with the basics.

Imagine that you have a large dataset, and you need to find a specific entry within it. Without an index, searching through all the data can be a time-consuming process with a worst-case time complexity of O(n). However, when an attribute is indexed in Rell, it dramatically enhances query performance, reducing the time complexity to O(log(n)), thanks to the underlying binary tree structure used by PostgreSQL.

In Rell, you have two options to mark an attribute for indexing: key and index. While both improve query performance, they serve different purposes:

Key

A key is an index that enforces a unique constraint on the indexed attribute. This uniqueness constraint guarantees that entities with the same attribute value will not exist in your database, making it less error-prone when creating entries.

Index

An index is used to improve query performance for non-unique attributes.

note

Note that both keys and indexes introduce extra time in creating entities, so be sure to mark your attributes cautiously.

Key and Index Example

Consider the scenario where multiple houses share the same street, and each house has its own unique id. This could be modeled like

entity house {
    key id: integer;
    index street: text;
}

Now, the following queries would be high performant:

val unique_house = house @ { .id == 10 };
val houses_on_main_street = house @* { .street == "main street" };

Composite Index

To further optimize your database queries, you can add a composite index. This is particularly useful when you need to find entries based on a combination of attributes. For instance, if your application often queries all houses owned by a specific owner on a particular street, you can create a multi-column index.

    index owner, street;

The order of the fields in a multi-column index is crucial for performance. This is exactly how composite indexes work in SQL, where each column has precedence over the latter. To create the most efficient index, place the most significant attribute first. For instance, if you want to query all houses with exactly four rooms and a floor area greater than 100:

house @ { .number_of_rooms == 4, .floor_area > 100 };

In this case, an optimal index configuration would be:

    index number_of_rooms, floor_area;

Composite keys work the same way. If a specific combination of entries can only exist once, adding a composite key will ensure this constraint in the database. Place the attribute yielding the fewest results first to achieve optimal performance.