Trino query on MongoDB is not using indexes

Viewed 24

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",
    "genericEvent": {
        "status": "create",
        "header": {
            "direction": "DISTRIBUTION",  
            "receiver": {
                "address": "215 LAMBTON QUAY"
            },
            "transferReference": "123"
        },  
    "statusDate": "2022-08-22T17:19:21.999"
    }
    
}
select * from schema.database.table where genericEvent.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.

0 Answers
Related