Keys and indexes

An index lets the database find data faster. This lesson covers how indexes work in Rell and when to use them.

Suppose you need to locate one entry in a large dataset. Without an index, the database may have to scan every row, which takes O(n) time in the worst case. When you index an attribute in Rell, lookups drop to O(log(n)), because PostgreSQL stores the index in a tree structure.

Rell gives you two ways to index an attribute: key and index. Both speed up queries, but each has a distinct role:

Key

A key is an index that also requires the attribute's value to be unique. Two entities can't share the same value for that attribute, which protects you from accidentally creating duplicate entries.

Index

Use an index to speed up queries on attributes whose values don't have to be unique.

Key and index example

Take a street with several houses on it, where each house has its own unique id. You can model this as follows:

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

With this model, the following queries run efficiently:

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

Composite index

A composite index covers several attributes at once and helps when you look up entries by a combination of values. If your app frequently fetches every house a given owner has on a given street, define a multi-column index:

index owner, street;

Column order in a multi-column index affects performance. As in SQL, the columns are ordered, and each one comes before the next in the index. Put the most significant attribute first to get the most efficient index. For example, to find all houses that have exactly four rooms and more than 100 units of floor area:

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

The best index for this query is:

index number_of_rooms, floor_area;

Composite keys follow the same rules. When a particular combination of values must be unique, a composite key enforces that in the database. For the best performance, list first the attribute that matches the fewest results.

Practical example

Here you create house records and then query for specific ones.

Open src/main/entities.rell and add the house entity:

entity house {  index street;  number: integer;  key street, number;  number_of_rooms: integer;  number_of_floors: integer;  floor_area: integer;  index number_of_rooms, floor_area;}

Next, add the following code to src/main/queries.rell so you can query house records:

query get_specific_houses(number_of_rooms: integer, floor_area: integer) = house @* { .number_of_rooms == number_of_rooms, .floor_area >= floor_area } ( $.to_struct() );

Start a local node so you can work with the database. Pass the --wipe flag when you want to clear all existing records:

chr node start --wipe

Create a street record:

chr tx create_street "Drottninggatan"

Call the create_house operation to add three houses:

chr tx create_house 1 30 4 1 200chr tx create_house 1 31 4 1 100chr tx create_house 1 32 4 1 109

Now query the database for specific houses:

chr query get_specific_houses

The query returns:

[  [    "floor_area": 200,    "number": 30,    "number_of_floors": 1,    "number_of_rooms": 4,    "street": 1  ],  [    "floor_area": 109,    "number": 32,    "number_of_floors": 1,    "number_of_rooms": 4,    "street": 1  ]]