Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MongoDB indexes don't work with prismagraphql/mongo-single-replica #5681

Open
hffmnn opened this issue Feb 7, 2024 · 0 comments
Open

MongoDB indexes don't work with prismagraphql/mongo-single-replica #5681

hffmnn opened this issue Feb 7, 2024 · 0 comments

Comments

@hffmnn
Copy link

hffmnn commented Feb 7, 2024

Hi.

I wanted to try out performance optimizations via indexes. As I wanted to start small, I used the example in databases/mongodb. In the folder I ran docker-compose --file docker-compose.yml up --detach followed by a

export DATABASE_URL="mongodb://root:prisma@localhost:27017/prisma-mongo?authSource=admin&retryWrites=true&w=majority"
yarn script:create-user

This worked as expected, the entry was in the collection prisma-mongo/User.

Then I added an index via the Mongodb client (yarn add [email protected]) followed by a

const client = new MongoClient('mongodb://root:prisma@localhost:27017/prisma-mongo?authSource=admin&retryWrites=true&w=majority')
await client.connect()
const database = client.db('prisma-mongo')
const peaks = database.collection('User')
await peaks.createIndex({ firstName: 1 })

This script hangs forever and never completes.

When looking at the indexes via MongoDB Compass it looks like the index is created correctly though:
Screenshot 2024-02-07 at 08 55 18

But this index never gets used. Doing a simple query like { firstName: { $eg: "Hana"} }, explain gives the following:

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "prisma-mongo.User",
    "indexFilterSet": false,
    "parsedQuery": {
      "firstName": { "$eq": "Hana" }
    },
    "winningPlan": {
      "stage": "COLLSCAN",
      "filter": {
        "firstName": { "$eq": "Hana" }
      },
      "direction": "forward"
    },
    "rejectedPlans": []
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1,
    "executionTimeMillis": 0,
    "totalKeysExamined": 0,
    "totalDocsExamined": 1,
    "executionStages": {
      "stage": "COLLSCAN",
      "filter": {
        "firstName": { "$eq": "Hana" }
      },
      "nReturned": 1,
      "executionTimeMillisEstimate": 0,
      "works": 3,
      "advanced": 1,
      "needTime": 1,
      "needYield": 0,
      "saveState": 0,
      "restoreState": 0,
      "isEOF": 1,
      "direction": "forward",
      "docsExamined": 1
    },
    "allPlansExecution": []
  },
  "serverInfo": {
    "host": "323b689e1af8",
    "port": 27017,
    "version": "4.4.3",
    "gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
  },
  "ok": 1,
  "$clusterTime": {
    "clusterTime": {
      "$timestamp": "7332765808688365569"
    },
    "signature": {
      "hash": "YjUc8VUByYLoiyVKDuFJkmgCTcQ=",
      "keyId": {
        "low": 4,
        "high": 1707291162,
        "unsigned": false
      }
    }
  },
  "operationTime": {
    "$timestamp": "7332765808688365569"
  }
}

So it uses a COLLSCAN instead of a IXSCAN.

When trying the same (The same means generating the index as described but generating the collection isn't done via prisma, because it needs a replica set.) against another mongo image (e.g.image: mongo:6-jammy) it works:

  • Creating the index doesn't hang but returns immediately
  • Doing the query gives IXSCAN

The only idea google came up with was this: https://stackoverflow.com/a/65699029 still I have no idea how to apply this.

Any ideas?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant