Skip to content

Indexing JSON Documents in Marten and Polecat

Jeremy Miller16th November 2026

Marten and Polecat are both covered by JasperFx Software support plans. If you've got a slow query in production and want a second set of eyes on it, that's exactly the kind of thing we do.

The first objection I heard about Marten back when it was nothing but a document database on top of PostgreSQL was some version of "sure, but how do you query that efficiently when everything is stuffed into one JSON column?" It's a fair question, and we still get that occasionally even now with Polecat on SQL Server too. Now of course, Marten is mostly used as a tool for Event Sourcing, but the projections within Marten and now Polecat sit right on top of the document database support -- and one of the things that makes Marten and Polecat such a powerful tool set is providing rich support for querying projection data right out of the gate.

The EF Core team is adding some sort of JSON indexing and more JSON querying to their PostgreSQL support as part of the forthcoming Npgsql 11.0 release (that Marten 10.0 will also consume). I'm very sympathetic to any potential difficulties they are most likely having because the INDEX delta detection is what I call a permutation hell kind of problem. However, it's fun right now to simply point out that Marten has had robust support for the PostgreSQL JSONB data type for over a decade including broad support for PostgreSQL indexing from the very beginning.

The short answer is that both databases can index values inside a JSON document, and both Marten and Polecat let you declare those indexes in a line of C# and then take care of building and changing them for you. PostgreSQL does it with expression indexes over the jsonb column. SQL Server does it with persisted computed columns over JSON_VALUE. You'd be using the exact same b-tree index you'd put on a relational column either way.

The longer answer is this post. I want to walk through the index options in both tools, and more importantly show you how to see what the database does with them, because an index that the query planner ignores is just a tax on your inserts.

All the code here is from a runnable sample, DocumentIndexing in the CritterStackSamples repository, with one console application for each tool. Every SQL statement, query plan, and row count in this post is real output from that sample against 100,000 documents.

The document ​

Nothing fancy, just an order with a nested address, a child collection of lines, and an array of tags:

csharp
public class Order
{
    public Guid Id { get; set; }
    public string OrderNumber { get; set; } = "";
    public string CustomerEmail { get; set; } = "";
    public string Region { get; set; } = "";
    public string Status { get; set; } = "";
    public DateTimeOffset PlacedAt { get; set; }
    public Address ShippingAddress { get; set; } = new();
    public List<OrderLine> Lines { get; set; } = [];
    public string[] Tags { get; set; } = [];
    public string DeliveryNotes { get; set; } = "";
}

public class Address
{
    public string City { get; set; } = "";
    public string PostalCode { get; set; } = "";
}

public class OrderLine
{
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

Start by looking at the query ​

Before adding a single index, let's get the diagnostics in place. Marten has had two little extension methods on IQueryable<T> for years that I think are badly underused. ToCommand() hands you the NpgsqlCommand Marten would run without running it, and ExplainAsync() asks PostgreSQL for the query plan:

csharp
var query = session.Query<Order>()
    .Where(x => x.CustomerEmail == "Customer42@Example.com");

// The SQL Marten will send, without sending it
var command = query.ToCommand(FetchType.FetchMany);
Console.WriteLine(command.CommandText);

// The PostgreSQL query plan. Analyze() actually runs the
// query so you get real timings and row counts
var plan = await query.ExplainAsync(configureExplain: x => x.Analyze());
Console.WriteLine($"{plan.NodeType}, {plan.ActualRows} rows, cost {plan.TotalCost}, {plan.ExecutionTime} ms");

With 100,000 orders in the table and nothing but the primary key, that prints:

select d.data from indexing.mt_doc_order as d where d.data ->> 'CustomerEmail' = :p0;
Seq Scan, 4 rows, cost 9651.00, 6.137 ms

A sequential scan. PostgreSQL read every one of the 100,000 documents, pulled CustomerEmail out of the JSON for each, and kept four of them. Six milliseconds doesn't sound scary on my laptop with a warm cache, but that number only goes one direction as the table grows.

One wart I have to own up to. The QueryPlan that comes back from ExplainAsync() tells you the node types, the costs, and the row counts, but it doesn't currently tell you which index was used (that's now an issue). So the sample also runs a plain old explain over that same command and prints what PostgreSQL has to say, and that's what you'll see in the rest of this post:

Seq Scan on mt_doc_order d
  Filter: ((data ->> 'CustomerEmail'::text) = 'Customer42@Example.com'::text)

Declaring indexes in Marten ​

Here's the whole index configuration for the Marten half of the sample. My personal preference has always been to keep this kind of thing in the fluent interface on StoreOptions rather than sprinkling attributes on the document type, but there are [UniqueIndex], [DuplicateField], and friends if you'd rather keep it next to the properties:

csharp
opts.Schema.For<Order>()

    // A basic index on one property of the JSON document
    .Index(x => x.CustomerEmail)

    // Any depth of nesting works the same way
    .Index(x => x.ShippingAddress.PostalCode)

    // One index over two properties. The order matters, just like
    // it does for any other composite b-tree index
    .Index(x => new { x.Region, x.Status })

    // A unique index. Marten will reject a second order
    // with the same order number
    .UniqueIndex(x => x.OrderNumber)

    // A partial index. Only the open orders are in it, so it
    // stays small no matter how many delivered orders pile up
    .Index(x => x.ShippingAddress.City, x =>
    {
        x.Name = "mt_doc_order_idx_open_by_city";
        x.Predicate = "(data ->> 'Status') = 'Open'";
    })

    // Computed indexes do not work for DateTimeOffset, so duplicate
    // the value into its own column and index that instead
    .Duplicate(x => x.PlacedAt, configure: x =>
    {
        x.SortOrder = SortOrder.Desc;
    })

    // A GIN index over the whole document...
    .GinIndexJsonData()

    // ...and a smaller one over just the Lines child collection
    .GinIndexJsonDataMember(x => x.Lines)

    // A full text index over one property, stored as GiST rather
    // than the default GIN
    .FullTextIndex(x => x.Method = IndexMethod.gist, x => x.DeliveryNotes);

Marten notices the difference ​

That configuration is just C# so far. The database still has the bare table from before. This is the part of Marten that I think folks coming from other tools underestimate: Marten (through Weasel) compares what your configuration says should exist to what's actually in the database, and works out the DDL to get from one to the other.

In development you never see this happen. With the default AutoCreate.CreateOrUpdate setting, Marten quietly applies the changes the first time you touch the document type. For this post I turned that off so I could ask for the migration and look at it:

csharp
var migration = await store.Storage.Database.CreateMigrationAsync();

var writer = new StringWriter();
migration.WriteAllUpdates(writer, store.Storage.Database.Migrator, AutoCreate.CreateOrUpdate);
Console.WriteLine(writer.ToString());

And here's what Marten found:

sql
alter table indexing.mt_doc_order add column placed_at timestamp with time zone NULL;
update indexing.mt_doc_order set "placed_at" = indexing.mt_immutable_timestamptz(data ->> 'PlacedAt');
CREATE INDEX mt_doc_order_idx_customer_email ON indexing.mt_doc_order USING btree ((data ->> 'CustomerEmail'));
CREATE INDEX mt_doc_order_idx_shipping_address_postal_code ON indexing.mt_doc_order USING btree ((data -> 'ShippingAddress' ->> 'PostalCode'));
CREATE INDEX mt_doc_order_idx_regionstatus ON indexing.mt_doc_order USING btree ((data ->> 'Region'), (data ->> 'Status'));
CREATE UNIQUE INDEX mt_doc_order_uidx_order_number ON indexing.mt_doc_order USING btree ((data ->> 'OrderNumber'));
CREATE INDEX mt_doc_order_idx_open_by_city ON indexing.mt_doc_order USING btree ((data -> 'ShippingAddress' ->> 'City')) WHERE ((data ->> 'Status') = 'Open');
CREATE INDEX mt_doc_order_idx_placed_at ON indexing.mt_doc_order USING btree (placed_at DESC);
CREATE INDEX mt_doc_order_idx_data ON indexing.mt_doc_order USING gin (data jsonb_path_ops);
CREATE INDEX mt_doc_order_idx_lines_gin ON indexing.mt_doc_order USING gin ((data -> 'Lines') jsonb_path_ops);
CREATE INDEX mt_doc_order_idx_fts ON indexing.mt_doc_order USING gist (to_tsvector('english',((data ->> 'DeliveryNotes'))));

Take a second with that, because it tells you exactly how this all works. A "computed index" in Marten is an ordinary PostgreSQL expression index where the expression is the path into the JSON. The duplicated field for PlacedAt is a real column, and notice that Marten wrote the update statement to backfill it from the documents that were already there.

It's not just additions either. Later on the sample changes its mind about the email index and makes it case insensitive by adding x.Casing = ComputedIndex.Casings.Lower. Ask for the migration again and you get only that one difference:

sql
drop index if exists indexing.mt_doc_order_idx_customer_email;
CREATE INDEX mt_doc_order_idx_customer_email ON indexing.mt_doc_order USING btree (lower(data ->> 'CustomerEmail'));

Same index name, different definition, and Marten caught it. The matching LINQ query is the one you'd guess, x.CustomerEmail.ToLower() == "customer42@example.com", which Marten turns into lower(d.data ->> 'CustomerEmail') = :p0 so that it lines up with the index expression.

If you're thinking to yourself, "I think I'll just vibe code me an equivalent tool," maybe think again. The index change detection for PostgreSQL has easily been one of the most challenging subsystems to build in the entire Critter Stack ecosystem.

In real life you'd do all this from the command line rather than in code. dotnet run -- db-patch writes that same delta to a SQL file with a rollback script beside it, db-apply just applies it, and db-assert fails your deployment pipeline if the database and the configuration have drifted apart. See Stateful Resources and Database Migrations for the whole story.

WARNING

The flip side of Marten managing indexes is that Marten owns the indexes on its tables. If your DBA adds an index by hand that Marten doesn't know about, the next migration is going to see it as drift and drop it. Declare it through Marten instead, or tell Marten to ignore that index. The same goes for Polecat.

What PostgreSQL does with them ​

Now the same queries again, with the indexes in place.

One property ​

select d.data from indexing.mt_doc_order as d where d.data ->> 'CustomerEmail' = :p0;

Index Scan using mt_doc_order_idx_customer_email on mt_doc_order d
  Index Cond: ((data ->> 'CustomerEmail'::text) = 'Customer42@Example.com'::text)

The estimated cost went from 9651 to 24.5, and the execution time from 6.137 ms to 0.030 ms. The SQL didn't change at all. What matters is that the expression in the where clause is the same expression the index was built on. Marten generates both of them, so they match.

A nested property is exactly the same thing with a longer path:

select d.data from indexing.mt_doc_order as d where d.data -> 'ShippingAddress' ->> 'PostalCode' = :p0;

Index Scan using mt_doc_order_idx_shipping_address_postal_code on mt_doc_order d
  Index Cond: (((data -> 'ShippingAddress'::text) ->> 'PostalCode'::text) = '40213'::text)

Multiple properties ​

Index(x => new { x.Region, x.Status }) built one b-tree over both values, and a query that filters on both uses it for both:

csharp
session.Query<Order>().Where(x => x.Region == "EMEA" && x.Status == OrderStatus.Shipped)
Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: (((data ->> 'Region'::text) = 'EMEA'::text) AND ((data ->> 'Status'::text) = 'Shipped'::text))
  ->  Bitmap Index Scan on mt_doc_order_idx_regionstatus
        Index Cond: (((data ->> 'Region'::text) = 'EMEA'::text) AND ((data ->> 'Status'::text) = 'Shipped'::text))

All the usual rules for composite indexes apply. A query on Region alone can use this index, and a query on Status alone mostly can't, so put the property you always filter on first.

A partial index ​

About 2% of the orders in the sample data are still open, which is pretty realistic. Most of what any long lived system is carrying around is finished business. A partial index only contains the rows that match its predicate, so an index over just the open orders stays tiny forever:

csharp
session.Query<Order>().Where(x => x.Status == OrderStatus.Open && x.ShippingAddress.City == "Oslo")
Index Scan using mt_doc_order_idx_open_by_city on mt_doc_order d
  Index Cond: (((data -> 'ShippingAddress'::text) ->> 'City'::text) = 'Oslo'::text)

Notice that the Status = 'Open' condition disappeared from the plan entirely. PostgreSQL worked out that every row in that index is already an open order. The Predicate is raw SQL that you write, so it's on you to write it the same way Marten writes the where clause. Guess what ToCommand() is good for?

DateTimeOffset values ​

This is the one real limitation of computed indexes in Marten. They don't work for DateTime or DateTimeOffset members, because PostgreSQL will only index an expression that is immutable and casting text to a timestamp isn't. For those, use a duplicated field, which copies the value into its own column on every write. When Marten sees a LINQ query against a duplicated member, it writes the SQL against the column instead of the JSON:

csharp
session.Query<Order>().OrderByDescending(x => x.PlacedAt).Take(10)
select d.data from indexing.mt_doc_order as d order by d.placed_at desc LIMIT :p0;

Limit
  ->  Index Scan using mt_doc_order_idx_placed_at on mt_doc_order d

The ten newest orders out of 100,000, with no sort step, because the index is already in descending order.

A unique index ​

A unique index is both a faster lookup and a business rule that the database enforces for you, even when two requests race each other. Try to store a second order with an order number that's already taken:

csharp
session.Store(new Order { Id = Guid.NewGuid(), OrderNumber = "ORD-000042" });
await session.SaveChangesAsync();
DocumentAlreadyExistsException: Document already exists MartenIndexing.Order: aa3d466c-ded2-493a-9d77-98b3381d2180

UniqueIndex() takes multiple properties too, and with conjoined multi-tenancy it can be scoped so that a value only has to be unique within one tenant. The unique index documentation has all the permutations.

Child collections, containment, and GIN ​

Everything in this section was hard to build and Marten has come a long ways in its ability to generate much more efficient SQL queries for sub-collection queries.

Now for my "favorite" part. Everything so far has been a b-tree over a single scalar value, which is the same thing you'd do with relational columns. But a document has structure, and the query I actually want to write against orders is "which orders have a line for this SKU?"

In a relational model, that's a join to an order_lines table with an index on sku. In Marten it's this:

csharp
session.Query<Order>().Where(x => x.Lines.Any(l => l.Sku == "SKU-0042"))

There's no order_lines table to index. What PostgreSQL has instead is the JSONB containment operator, @>, which asks "does the JSON on the left contain the JSON on the right?" And Marten translates an equality test inside Any() into exactly that:

select d.data from indexing.mt_doc_order as d where d.data -> 'Lines' @> :p0;
   p0 = [{"Sku":"SKU-0042"}]

Read that parameter as "an array with at least one element that has a Sku of SKU-0042." And containment is precisely the operation that a GIN index exists to speed up. That's what GinIndexJsonDataMember(x => x.Lines) created, and here it is at work:

Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb)
  ->  Bitmap Index Scan on mt_doc_order_idx_lines_gin
        Index Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb)

118 orders out of 100,000 in 0.3 ms, with no join and no second table. Without that index, the same query was a sequential scan that took around 24 ms.

The nice thing about a GIN index is that it isn't tied to one property the way a b-tree is. That one index over Lines serves any equality question you ask of a line. Add a second condition with && and it just becomes a bigger fragment to look for:

csharp
session.Query<Order>().Where(x => x.Lines.Any(l => l.Sku == "SKU-0042" && l.Quantity == 12))
select d.data from indexing.mt_doc_order as d where d.data -> 'Lines' @> :p0;
   p0 = [{"Sku":"SKU-0042","Quantity":12}]

An || becomes two containment checks, and PostgreSQL combines two passes over the same index:

Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: (((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb) OR ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0043"}]'::jsonb))
  ->  BitmapOr
        ->  Bitmap Index Scan on mt_doc_order_idx_lines_gin
              Index Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb)
        ->  Bitmap Index Scan on mt_doc_order_idx_lines_gin
              Index Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0043"}]'::jsonb)

When containment isn't enough: JSONPath ​

JSONPath filtering is more important in Polecat than Marten, but we certainly use it in both tools.

Containment can only express "equals." The moment you ask for a range, like an order with a line for ten or more of a SKU, there's no JSON fragment that means that. For those predicates Marten switches to SQL/JSON path expressions:

csharp
session.Query<Order>().Where(x => x.Lines.Any(l => l.Sku == "SKU-0042" && l.Quantity >= 10))
select d.data from indexing.mt_doc_order as d
where (d.data -> 'Lines' @> :p0
  and jsonb_path_exists(d.data, '$.Lines[*] ? ((@.Sku == $val1 && @.Quantity >= $val2))', :p1));
   p0 = [{"Sku":"SKU-0042"}]
   p1 = {"val1":"SKU-0042","val2":10}

There are two things going on in that SQL, and I think it's some of the cleverest work in Marten's LINQ provider. The jsonb_path_exists() call is the real, exact answer to the question. A GIN index can't help with it. But Marten noticed that part of the predicate is an equality, so it also emits a containment check for just that part as a pre-filter. The plan shows the division of labor:

Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb)
  Filter: jsonb_path_exists(data, '$."Lines"[*]?(@."Sku" == $"val1" && @."Quantity" >= $"val2")'::jsonpath, '{"val1": "SKU-0042", "val2": 10}'::jsonb, false)
  ->  Bitmap Index Scan on mt_doc_order_idx_lines_gin
        Index Cond: ((data -> 'Lines'::text) @> '[{"Sku": "SKU-0042"}]'::jsonb)

The GIN index narrows 100,000 orders down to the 118 that mention the SKU at all, and the JSONPath filter only has to run against those. A predicate with no equality in it at all, say x.Lines.Any(l => l.Quantity >= 10), has nothing for the index to grab onto and will scan. The Marten documentation has a full table of which predicate shapes translate to which strategy, and it's worth a look before you design a query that has to be fast.

The whole document or one member? ​

I declared two GIN indexes above, and they're good for different queries.

GinIndexJsonDataMember(x => x.Lines) indexes the expression data -> 'Lines'. It's the one that serves the Any() queries, because that's the expression on the left side of the @> in the SQL Marten generates. It's also a lot smaller than indexing everything.

GinIndexJsonData() indexes the entire document. It serves queries where the containment check is anchored at the root of the document, and the common case of that is Contains() on an array of simple values:

csharp
session.Query<Order>().Where(x => x.Tags.Contains("backorder") && x.Tags.Contains("international"))
select d.data from indexing.mt_doc_order as d where (d.data @> :p0 and d.data @> :p1);
   p0 = {"Tags":["backorder"]}
   p1 = {"Tags":["international"]}

Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: ((data @> '{"Tags": ["backorder"]}'::jsonb) AND (data @> '{"Tags": ["international"]}'::jsonb))
  ->  Bitmap Index Scan on mt_doc_order_idx_data
        Index Cond: ((data @> '{"Tags": ["backorder"]}'::jsonb) AND (data @> '{"Tags": ["international"]}'::jsonb))

Neither index can stand in for the other. I'll admit that this is the kind of detail I'd never keep straight from memory, which is the whole point of running the explain plan instead of guessing.

A whole document GIN index isn't free. It's the largest index you can put on a document table and every write has to update it, so I'd only add one for a query you've actually measured.

TIP

If you declare both, call GinIndexJsonData() before GinIndexJsonDataMember(). Writing this post turned up a bug where the opposite order quietly replaces the member index with the whole document one. And yes, I found that by looking at the explain plan and seeing a sequential scan where I expected an index.

Where GiST fits ​

Marten's documentation has long had a page titled "GIN or GiST Indexes," but let's be more careful about what GiST is for. PostgreSQL has no GiST operator class for jsonb itself, so GIN is the only choice for the containment queries above. GiST comes into play for the values you derive from a document, and the most common one is full text search. A tsvector can be indexed with either method, and Marten's FullTextIndex() lets you pick:

csharp
.FullTextIndex(x => x.Method = IndexMethod.gist, x => x.DeliveryNotes)
csharp
session.Query<Order>().Where(x => x.PlainTextSearch("neighbor"))
select d.data from indexing.mt_doc_order as d
where to_tsvector('english'::regconfig, ((d.data ->> 'DeliveryNotes'))) @@ plainto_tsquery('english'::regconfig, :p0);

Bitmap Heap Scan on mt_doc_order d
  Recheck Cond: (to_tsvector('english'::regconfig, (data ->> 'DeliveryNotes'::text)) @@ '''neighbor'''::tsquery)
  ->  Bitmap Index Scan on mt_doc_order_idx_fts
        Index Cond: (to_tsvector('english'::regconfig, (data ->> 'DeliveryNotes'::text)) @@ '''neighbor'''::tsquery)

The PostgreSQL guidance is that GIN is the preferred type for text search and that's Marten's default. GiST indexes are lossy, so they have to recheck more rows, but they're cheaper to update. It's there if your workload is write heavy and you've measured the difference. There's much more on search in Full Text, Vector, and Hybrid Search Across the Critter Stack.

The same story on SQL Server with Polecat ​

Polecat is our Marten-alike for SQL Server 2025, and I do mean alike. Here's the Polecat half of the sample:

csharp
opts.Schema.For<Order>()

    // A basic index on one property of the JSON document
    .Index(x => x.CustomerEmail)

    // Any depth of nesting works the same way
    .Index(x => x.ShippingAddress.PostalCode)

    // One index over two properties
    .Index(x => new { x.Region, x.Status })

    // A unique index. SQL Server will reject a second order
    // with the same order number
    .UniqueIndex(x => x.OrderNumber)

    // A covering index. City is the key, and Region and Status ride along
    // as INCLUDE columns so some queries never have to touch the table
    .Index(x => x.ShippingAddress.City, include: x => new { x.Region, x.Status })

    // Tell Polecat the real SQL type of the value, and sort it descending
    .Index(x => x.PlacedAt, x =>
    {
        x.SqlType = "datetimeoffset";
        x.SortOrder = SortOrder.Descending;
    });

If you can read the Marten version, you can read that. What's different is what happens underneath, because SQL Server doesn't have expression indexes. The idiomatic way to index into JSON in SQL Server is a persisted computed column, and that's what Polecat builds. Here's the migration Polecat detected for that configuration, fetched with the same CreateMigrationAsync() call as before since both tools sit on top of Weasel:

sql
alter table indexing.pc_doc_order add cc_customeremail AS (JSON_VALUE(data, '$.customerEmail' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_shippingaddress_postalcode AS (JSON_VALUE(data, '$.shippingAddress.postalCode' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_region AS (JSON_VALUE(data, '$.region' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_status AS (JSON_VALUE(data, '$.status' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_ordernumber AS (JSON_VALUE(data, '$.orderNumber' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_shippingaddress_city AS (JSON_VALUE(data, '$.shippingAddress.city' RETURNING varchar(250))) PERSISTED;
alter table indexing.pc_doc_order add cc_placedat AS (CONVERT(datetimeoffset, JSON_VALUE(data, '$.placedAt'), 126)) PERSISTED;

CREATE INDEX ix_pc_doc_order_customeremail ON indexing.pc_doc_order (cc_customeremail);
CREATE INDEX ix_pc_doc_order_shippingaddress_postalcode ON indexing.pc_doc_order (cc_shippingaddress_postalcode);
CREATE INDEX ix_pc_doc_order_region_status ON indexing.pc_doc_order (cc_region, cc_status);
CREATE UNIQUE INDEX ux_pc_doc_order_ordernumber ON indexing.pc_doc_order (cc_ordernumber);
CREATE INDEX ix_pc_doc_order_shippingaddress_city ON indexing.pc_doc_order (cc_shippingaddress_city) INCLUDE (cc_region, cc_status);
CREATE INDEX ix_pc_doc_order_placedat ON indexing.pc_doc_order (cc_placedat DESC);

I trimmed the IF NOT EXISTS guards that Polecat wraps around each CREATE INDEX so the script can be run twice.

A few SQL Server specific things to point out:

  • The INCLUDE columns are a SQL Server feature that PostgreSQL folks will know as covering indexes. Polecat exposes them directly.
  • DateTimeOffset isn't a special case here. You tell Polecat the SQL type and it builds a typed computed column, no duplicated field required.
  • String members are indexed as varchar(250) by default. If the value can hold text outside of ASCII, set x.SqlType = "nvarchar(250)". The Polecat documentation on indexing string members goes deep on why, and it's the one page I'd ask you to read before indexing a name or an address.
  • SQL Server's default collation is case insensitive, so the lower cased index I showed for Marten is usually something you just don't need.

Seeing the SQL and the plan ​

Polecat's SQL preview hangs off the session:

csharp
var sql = session.ToSql(
    session.Query<Order>().Where(x => x.CustomerEmail == "Customer42@Example.com"));

For the plan, Polecat goes a step further than Marten's ExplainAsync(). It asks SQL Server for the actual execution plan, which is a pile of XML that nobody wants to read, and boils it down to the questions you'd be asking of it anyway:

csharp
var plan = await ((IDocumentStoreDiagnostics)store).ExplainDocumentQueryAsync(
    typeof(Order).FullName!,
    new DocumentQueryOptions(1, 1000)
    {
        Where = "CustomerEmail = @0",
        Arguments = ["Customer42@Example.com"]
    },
    CancellationToken.None);

Console.WriteLine($"Indexes used:  {string.Join(", ", plan.IndexesUsed)}");
Console.WriteLine($"Scans table:   {plan.ScansTable}");
Console.WriteLine($"Rows examined: {plan.RowsExamined}");
Console.WriteLine($"Rows returned: {plan.RowsReturned}");

That API takes its filter as text rather than as an IQueryable, because it was built for tooling to call. The raw XML is still there in plan.PlanText if you want to paste it into SSMS.

Before the indexes existed:

SELECT data FROM [indexing].[pc_doc_order] WHERE (JSON_VALUE(data, '$.customerEmail') = @p0 AND ...)

Indexes used:  pkey_pc_doc_order_id
Scans table:   True
Rows examined: 100000
Rows returned: 4

And after:

SELECT data FROM [indexing].[pc_doc_order]
WHERE (JSON_VALUE(data, '$.customerEmail' RETURNING varchar(250)) = @p0 AND ...)

Indexes used:  ix_pc_doc_order_customeremail, pkey_pc_doc_order_id
Scans table:   False
Rows examined: 4
Rows returned: 4

Ouch. It read 100,000 rows to find four, and then it read four rows to find four. Notice that the SQL changed this time. Once Polecat knows there's a computed column for a member, it writes the where clause with the same JSON_VALUE(... RETURNING varchar(250)) expression as the column so that SQL Server will match the two up. (I cut the tail off of those statements. Polecat adds a couple of extra comparisons to make sure a varchar index never returns a wrong answer for Unicode text.)

The multi-property index does the same job as its Marten twin:

-- Open orders in EMEA
Indexes used:  ix_pc_doc_order_region_status, pkey_pc_doc_order_id
Scans table:   False
Rows examined: 491
Rows returned: 491

And the unique index is enforced by SQL Server itself:

SqlException: Cannot insert duplicate key row in object 'indexing.pc_doc_order' with unique index
'ux_pc_doc_order_ordernumber'. The duplicate key value is (ORD-000042).

Polecat detects changes too ​

Stage three of the Polecat sample takes the advice above and changes the email index to nvarchar(250). A computed column's definition can't be altered in place, so the migration Polecat generates drops the index, drops the column, and builds them both again (trimmed a little again):

sql
drop index if exists ix_pc_doc_order_customeremail on indexing.pc_doc_order;
alter table indexing.pc_doc_order drop column cc_customeremail;
alter table indexing.pc_doc_order add cc_customeremail AS (JSON_VALUE(data, '$.customerEmail' RETURNING nvarchar(250))) PERSISTED;
CREATE INDEX ix_pc_doc_order_customeremail ON indexing.pc_doc_order (cc_customeremail);

The same db-patch, db-apply, and db-assert commands work against Polecat, because it's the same Weasel machinery underneath with a SQL Server dialect.

Where Polecat is behind Marten ​

I'd rather tell you this than have you find out the hard way: the child collection story is where the two tools aren't equal today, and it's mostly about what the two databases can do.

Polecat supports Contains() on an array of simple values, but it runs as an OPENJSON subquery that no index can help:

SELECT data FROM [indexing].[pc_doc_order] WHERE @p0 + N'.' IN (SELECT [value] + N'.' FROM OPENJSON(data, '$.tags'))

Indexes used:  pkey_pc_doc_order_id
Scans table:   True

And Any() with a predicate over a collection of child objects isn't translated by Polecat at all right now. You'll get a BadLinqExpressionException telling you so, rather than a slow query.

SQL Server 2025 does have a native JSON index, and Polecat can create one with JsonIndex(). It's the closest thing to a GIN index on that side of the house. But as the Polecat documentation says plainly, SQL Server only uses that index for JSON_CONTAINS, and Polecat's LINQ provider doesn't emit JSON_CONTAINS yet. For now it only pays off for SQL you write yourself through session.AdvancedSql. If you've got queries into child collections that have to be fast on SQL Server today, my advice is to model that data as its own document type with a computed index on it.

Wrapping up ​

Here's the cheat sheet version:

You're askingMarten on PostgreSQLPolecat on SQL Server
One property equals or ranges over a valueIndex(x => x.Prop)Index(x => x.Prop)
Several properties togetherIndex(x => new { x.A, x.B })Index(x => new { x.A, x.B })
A value must be uniqueUniqueIndex(x => x.Prop)UniqueIndex(x => x.Prop)
Only a small, hot subset of documentsIndex() with a PredicateModel the subset separately
Date and time valuesDuplicate(x => x.Prop)Index() with a SqlType
Avoid going back to the tablenot covered hereIndex(..., include: ...)
A child collection has an element where...GinIndexJsonDataMember(x => x.Children)Not indexable through LINQ today
An array of values contains...GinIndexJsonData()Not indexable through LINQ today
Full text searchFullTextIndex(), GIN or GiSTSee the search post

And the three habits I'd actually want you to take away from this:

  1. Don't index anything until you've looked at a query. Every index is paid for on every insert and update. ToCommand() and ExplainAsync() in Marten, and ToSql() and the explain diagnostics in Polecat, are right there and take a minute to use.
  2. Check that the planner agrees with you. Twice while writing this post I declared an index, felt good about it, and then watched the plan come back with a table scan anyway. Both times the tooling told me the truth in a couple of seconds.
  3. Let the tools manage the schema. You change a line of C#, and Marten or Polecat works out the CREATE INDEX, the DROP INDEX, and the backfill. Use db-patch and db-assert to keep production honest.

The Marten documentation on indexing and diagnostics, and the Polecat documentation on indexing and diagnostics, go further than I could here.

Lastly, if you're staring at a slow query in a Marten or Polecat system and none of this has cracked it, that's a big part of what a JasperFx support plan is for. We've spent ten years looking at these query plans and we're happy to look at yours.