PK index change marten v6 => v7 makes querry's on id no longer hit a index

[schadowfax]

When you create a table or when you migrate from V6 to V7 there is a change on the PK

v6
CREATE UNIQUE INDEX pkey_mt_doc_mytable_id_tenant_id ON public.mt_doc_mytable USING btree (id, tenant_id)

V7
CREATE UNIQUE INDEX pkey_mt_doc_mytable_tenant_id_id ON public.mt_doc_mytable USING btree (tenant_id, id)

This now make querry's that use AnyTenant and still search on id very slow since the PK index is not hit any more.

        var mytableResult= await querySession
            .Query<MyTable>()
            .Where(x => x.AnyTenant())
            .FirstOrDefaultAsync(x => x.Id == id);

        select * from  public.mt_doc_mytable
        where id = '**'

Querry's that use 'AnyTenant()' but not search on id's are faster with this change.

proposel is to add a index on Id by default, or at least document this in de migration guid and in general documentation that you for some cases better ad this index manually.

[jeremydmiller]

@schadowfax Sigh. It's missing from the docs apparently, but you can opt into the pre-7 behavior here:

builder.Services.AddMarten(opts => {
     // other stuff
     opts.PrimaryKeyTenancyOrdering = PrimaryKeyTenancyOrdering.Id_Then_TenantId;
});

You're going to have to pick your poison. Some queries are much slower either way.

Plenty happy for a PR to improve the documentation, but I don't think anything is going to change here w/ that switch. Any new index on the events table would need to be "opt in" as to not change existing users. I'm happy to take that PR as well if you'd like.

[schadowfax]

Just notice that when you have the default V7 behaviour on PK what basicly is

    options.Policies.ForAllDocuments(m =>
    {
        m.PrimaryKeyTenancyOrdering = PrimaryKeyTenancyOrdering.TenantId_Then_Id;
    });

you still got a single index on tenant_id but why? Since it is generally unnecessary to have a separate index on tenant_id if it is already the first column in a composite (primary) key index. The primary key index can be used for queries filtering by tenant_id.

It looks this tenant_id is also added if you use the PrimaryKeyTenancyOrdering.Id_Then_TenantId and then ofcourse its relevant but it might be a good idea to in case of the default add a index on id and not on tenant_id.

seems this behaviour comes from the the DocumentTable where perhaps that index that is added can be changed from the tenant_id in the one case to the id column in the other case. Let me know what you guys think.