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.