CODEWITHSUNDEEP
mongodbtrino
Tilak
Tilak

Reputation: 373

Trino query on MongoDB is not using indexes

Querying mongo data from Trino results in collection scan instead of index scan. Please suggest.

For example, lets say we have below json in mongo

{
    "_id": "b4dc8c1d-feb0-41ad-826b-55de1826d63f",
    "event": {
        "status": "create",
        "header": {
            "direction": "DISTRIBUTION",
            "receiver": {
                "address": "street 1"
            },
            "transferReference": "123"
        }
    },
    "extractedFields": [
        {
            "field1": [
                "value1"
            ],
            "field2": [
                "value2"
            ]
        }
    ],
    "domainMetaData": {
      "receivedTime": {
        "$date": {
          "$numberLong": "1666571260554"
        }
      }
    }
    "statusDate": "2022-08-22T17:19:21.999"
}

select * from schema.database.table where event.header.transferReference = '123'

Field 'genericEvent.header.transferReference' is indexed in Mongo. Explain plan shows filter is pushed to mongo but entire collection is scanned.

ScanFilter[table = schema.database.table, grouped = false, filterPredicate = ("genericevent"[2][3] = VARCHAR '123')]

SELECT * FROM schema.database.tablewhere where statusDate  = timestamp '2022-08-22 17:19:21.999'

Field statusDate is indexed in Mongo. Explain plan shows filter is pushed to mongo but entire collection is scanned.

SELECT * FROM schema.database.tablewhere where _id  = 'b4dc8c1d-feb0-41ad-826b-55de1826d63f'

Above SQL query works fine. Index is used and only one record is loaded.

Upvotes: 0

Views: 1485

Answers (1)

Manfred Moser
Manfred Moser

Reputation: 29912

Index scans are not supported by the MongoDB connector. This is a performance improvement that could be added, but is currently not in place.

Upvotes: 0

Related Questions