Inefficient query translations

[86label]

Hi Prisma,

We hope you can help us!
We’re running Prisma 1.34, with a postgres cluster. We use prisma deploy to persist our datamodel, based on the schema below.
We’re experiencing poor performance, and through slow queries logging and checking postgres logs, we found that prisma translates a query into a very ineffective multi-part select statement where one part joins a table with itself and then sorts on the selected unique id (there will always be 0 or 1 returned).

Is there something we’re missing on how to design the schema or write the query that could help us help prisma/postgres create efficient statements?

We’re not using the ID the prisma way, as the ID length limit was blocking us from using some ID’s that we needed.

Schemas relevant for this example:

type Person  
{
txPrimaryKey: String @unique
id: ID! @id
PersonName: [PersonName]  @relation(name:"Person_PersonName")
}

type PersonName  
{
txPrimaryKey: String @unique
id: ID! @id
givenName: String
surname: String
Person: Person @relation(name:"Person_PersonName")
}

Query example:

query {
  person(where: { txPrimaryKey: "ABC:123" }) {
    genderCode
    PersonName {
      surname
      givenName
    }
  }
}

Which becomes:

select 
  ""default$default"".""Person"".""id"", 
  ""default$default"".""Person"".""genderCode"", 
from ""default$default"".""Person""
where ""default$default"".""Person"".""txPrimaryKey"" = $1","parameters: $1 = 'ABC:123'"

Plus:

select 
  "Alias"."surname", 
  "Alias"."givenName", 
  "Alias"."id", 
  "RelationTable"."id" as "__RelatedModel__", 
  "RelationTable"."Person" as "__ParentModel__"
from "default$default"."PersonName" as "Alias"
  join "default$default"."PersonName" as "RelationTable"
  on "Alias"."id" = "RelationTable"."id"
where "RelationTable"."Person" in ($1)
order by "RelationTable"."id" asc

Performing a postgres explain on the latter part with the join, we see that postgres spends a lot of time on sorting. We also don’t quite understand why a join is necessary, since all the information is from the same row of a unique ID.

Postgres Explain:

Sort  (cost=166500.88..166500.88 rows=1 width=148)
Sort Key: "Alias".id
->  Nested Loop  (cost=1000.55..166500.87 rows=1 width=148)
->  Gather  (cost=1000.00..166492.29 rows=1 width=52)
Workers Planned: 2
->  Parallel Seq Scan on "PersonName" "RelationTable"  (cost=0.00..165492.19 rows=1 width=52)
Filter: (("Person")::text = 'ABC:123'::text)
->  Index Scan using "PersonName_pkey" on "PersonName" "Alias"  (cost=0.56..8.57 rows=1 width=96)
Index Cond: ((id)::text = ("RelationTable".id)::text)

[nikolasburk]

Hey @86label, thanks a lot for opening this issue and apologies for the delayed response on this!

Unfortunately, this issue is a limitation of the current version of Prisma 1 that we won't be able to fix as we're running Prisma 1 in maintenance mode 😞Note that this will be fixed in Prisma 2 which will be released early next year.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.