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.

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.

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)))

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}