ME: Many-to-many table indices sub-optimal

[pimeys]

Let's imagine a model:

model User {
  id         Int       @id
  friendWith User[]    @relation("FriendShip")
  friendOf   User[]    @relation("FriendShip")
}

In this case we have a many-to-many relation, and we create an extra table for the relations:

         Table "_FriendShip"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 A      | integer |           | not null |
 B      | integer |           | not null |

And a few constraints:

Indexes:
    "_FriendShip_AB_unique" UNIQUE, btree ("A", "B")
Foreign-key constraints:
    "_FriendShip_A_fkey" FOREIGN KEY ("A") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "_FriendShip_B_fkey" FOREIGN KEY ("B") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE

Now everything works fine when selecting the friendWith attribute, due to us doing a search with the column A. But when using the friendOf attribute, we suddenly query the table from the column B causing a sub-optimal performance.

We should always add an additional index for the column B to prevent situations like this.

[do4gr]

Prisma 1.0 and 1.1 had both the unqiue(A,B) and index(B). I agree that we should re-introduce that.

[pimeys]

The same bug applies in cases where we have a many-to-many relationship between two separate tables.