Not support `$project` some specified column when `SELECT` part of columns

[zhang699]

I have investigate the actual mongo command that issued from mongo_fdw when using SELECT _id FROM warehouse

by setting following in mongodb, we can examine actual commands from mongo side

db.setProfilingLevel(2)
db.collection("system.profile").sort({"ts": -1})

even if only SELECT _id FROM warehouse, it issue the commands seems return entire document without $project in aggregation pipeline,

"command" : {
        "aggregate" : "warehouse",
        "pipeline" : [ 
            {
                "$match" : {}
            }
        ],
        "cursor" : {},
        "$db" : "db",
        "lsid" : {
            "id" : UUID("0c5b5caf-229b-4db3-b66a-04305ac9c4e5")
        }
    },

I think it cost much IO (network/disk) time to transfer unwanted data that postgresssql side doen't ask. Could mongo_fdw needs support that in the near future ?
or correct me If I forgot to configure something to use $project, I use meta driver and mongoc is 1.17.3

[vaibhavdalvi93]

Hi @zhang699

correct me If I forgot to configure something to use $project, I use meta driver and mongoc is 1.17.3

Your observation is correct. Currently, there is NO way to use $project.

The mongo_fdw don't support column push down. The reason behind this is mentioned in one of the comments in the code i.e. column push down resulting in performance degradation. The comment says,

`
/*

• We construct the query document to have MongoDB filter its rows. We
• could also construct a column name document here to retrieve only
• the needed columns. However, we found this optimization to degrade
• performance on the MongoDB server-side, so we instead filter out
• columns on our side. */ `

I think it cost much IO (network/disk) time to transfer unwanted data that postgresssql side doen't ask. Could mongo_fdw needs support that in the near future ?

We have to do more study around this to come to the conclusion. We have added this to the TO-DO list. We will work on this in near future.

[zhang699]

Thanks ！！