I am trying to implement a multi-column index using Postgres and Prisma.
I have defined a multi column index as shown in the Prisma documentation
model MyModel {
fieldA String!
fieldB String!
@@index(fields: [fieldA, fieldB])
@@map("my_models")
}
It appears to succeed since I can use Prisma to auto-generate a database migration which has a line something like this:
CREATE INDEX "my_models_fieldA_fieldB_idx" ON "my_models"("fieldA", "fieldB");
However, when I can't figure out how to use the Prisma client to query on this index. I have tried:
const content_node = await this.prismaService.myModel.findMany({
where: {
fieldA_fieldB: {
fieldA: input.fieldA,
filedB: input.fieldB,
},
},
})
And get the error:
node_modules/.prisma/client/index.d.ts:4925:5
4925 where?: MyModelWhereInput
~~~~~
The expected type comes from property 'where' which is declared here on type ...
(...)
After reading through (the auto-generated) MyModelWhereInput
I don't see any sign of the new index.
How can I use Prisma to query over this index?
EDIT: I can successfully do a filtered query like so:
const content_node = await this.prismaService.myModel.findMany({
where: {
fieldA: input.fieldA,
filedB: input.fieldB,
},
})
But my investigation of the SQL commands that get executed showed 'QUERY PLAN': 'Seq Scan on my_models'
. What abstraction level decides whether to use a sequential scan or an index scan? Should I expect my program to switch strategy once the database gets larger? (I currently have less than 100 items in the database).
I was having this same issue. I solved this by adding a unique constraint on fieldA
and fieldB
like @@unique([fieldA, fieldB])
. I also added a named index to my model. In your scenario, it would look like the following:
@@unique([fieldA, fieldB])
@@index([fieldA, fieldB], name: "fieldA_fieldB")