Calculated Index

INFO

Calculated indexes are a great way to optimize the querying of a document type without incurring potentially expensive schema changes and extra runtime insert costs.

If you want to optimize a document type for searches on a certain field within the JSON body without incurring the potential cost of the duplicated field, you can take advantage of Postgresql's computed index feature within Marten with this syntax:

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);

    _.DatabaseSchemaName = "examples";

    // This creates
    _.Schema.For<User>().Index(x => x.UserName);
});

using (var session = store.QuerySession())
{
    // Postgresql will be able to use the computed
    // index generated from above
    var somebody = session
        .Query<User>()
        .FirstOrDefault(x => x.UserName == "somebody");
}

^{snippet source | anchor}

In the configuration shown above, Marten generates a database index in Postgresql:

CREATE INDEX mt_doc_user_idx_user_name ON public.mt_doc_user ((data ->> 'UserName'));

You can also create calculated indexes for deep or nested properties like this:

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);

    _.Schema.For<Target>().Index(x => x.Inner.Color);
});

^{snippet source | anchor}

The configuration above creates an index like this:

CREATE INDEX mt_doc_target_idx_inner_color ON public.mt_doc_target (((data -> 'Inner' ->> 'Color')::int));

Or create calculated multi-property indexes like this:

var store = DocumentStore.For(_ =>
{
    var columns = new Expression<Func<User, object>>[]
    {
        x => x.FirstName,
        x => x.LastName
    };
    _.Schema.For<User>().Index(columns);
});

^{snippet source | anchor}

The configuration above creates an index like this:

CREATE INDEX mt_doc_user_idx_first_namelast_name ON public.mt_doc_user USING btree (((data ->> 'FirstName'::text)), ((data ->> 'LastName'::text)))

Indexing (parts of) date values

WARNING

At this time, calculated indexes do not work against DateTime or DateTimeOffset fields. You will have to resort to a duplicated field for these types.

When you need to query by a component of a DateOnly or DateTime value,
you can expose that component as a simple read only property and index it.

public required DateOnly From { get; set; }

public int FromYear => From.Year;

Multi-Column Indexes 7.0

You can specify multi-field computed indexes through anonymous types like so:

var store = DocumentStore.For(opts =>
{
    // This creates a single index against both FirstName and ListName
    opts.Schema.For<User>().Index(x => new { x.FirstName, x.LastName });
});

^{snippet source | anchor}

Customizing a Calculated Index

You have some ability to customize the calculated index by passing a second Lambda Action into the Index() method as shown below:

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);

    // The second, optional argument to Index()
    // allows you to customize the calculated index
    _.Schema.For<Target>().Index(x => x.Number, x =>
    {
        // Change the index method to "brin"
        x.Method = IndexMethod.brin;

        // Force the index to be generated with casing rules
        x.Casing = ComputedIndex.Casings.Lower;

        // Override the index name if you want
        x.Name = "mt_my_name";

        // Toggle whether or not the index is concurrent
        // Default is false
        x.IsConcurrent = true;

        // Toggle whether or not the index is a UNIQUE
        // index
        x.IsUnique = true;

        // Toggle whether index value will be constrained unique in scope of whole document table (Global)
        // or in a scope of a single tenant (PerTenant)
        // Default is Global
        x.TenancyScope = Marten.Schema.Indexing.Unique.TenancyScope.PerTenant;

        // Partial index by supplying a condition
        x.Predicate = "(data ->> 'Number')::int > 10";
    });

    // For B-tree indexes, it's also possible to change
    // the sort order from the default of "ascending"
    _.Schema.For<User>().Index(x => x.LastName, x =>
    {
        // Change the index method to "brin"
        x.SortOrder = SortOrder.Desc;
    });
});

^{snippet source | anchor}