Fork me on GitHub

Duplicated Fields for Faster Querying Edit on GitHub


According to our testing, the single best thing you can do to speed up queries against the JSONB documents is to duplicate a property or field within the JSONB structure as a separate database column on the document table. When you issue a Linq query using this duplicated property or field, Marten is able to write the SQL query to run against the duplicated field instead of using JSONB operators. This of course only helps for queries using the duplicated field.

To create a duplicated field, you can use the [DuplicateField] attribute like this:


[PropertySearching(PropertySearching.ContainmentOperator)]
public class Employee
{
    public int Id;

    // You can optionally override the Postgresql
    // type for the duplicated column in the document
    // storage table
    [DuplicateField(PgType = "text")]
    public string Category;
}


Or by using the fluent interface off of StoreOptions:


public class IndexExamples : MartenRegistry
{
    public IndexExamples()
    {
        // Add a gin index to the User document type
        For<User>().GinIndexJsonData();

        // Adds a basic btree index to the duplicated
        // field for this property that also overrides
        // the Postgresql database type for the column
        For<User>().Duplicate(x => x.FirstName, pgType: "varchar(50)");

        // Customize the index on the duplicated field
        // for FirstName
        For<User>().Duplicate(x => x.FirstName, configure: idx =>
        {
            idx.IndexName = "idx_special";
            idx.Method = IndexMethod.hash;
        });

        // Customize the index on the duplicated field
        // for UserName to be unique
        For<User>().Duplicate(x => x.UserName, configure: idx =>
        {
            idx.IsUnique = true;
        });
    }
}


By default, Marten adds a btree index (the Postgresql default) to a searchable index, but you can also customize the generated index with the syntax shown above: The second nested closure argument is an optional mechanism to customize the database index generated for the duplicated field.

In the case above, Marten would add an extra column to the generated mt_doc_user table called first_name. Some users find duplicated fields to be useful for user supplied SQL queries.