search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
326 stars 70 forks source link

Pushing down ORDER BY queries #161

Open iosifnicolae2 opened 2 years ago

iosifnicolae2 commented 2 years ago

Use case

I have the following query:

SELECT
          *
        FROM
          "unit"."record"
        ORDER BY
          "timestamp" DESC NULLS FIRST
        LIMIT
          1

QUERY PLAN: 

Limit  (cost=1030.00..1030.00 rows=1 width=168)
Output: _id, unit_id, "timestamp", data
->  Sort  (cost=1030.00..1032.50 rows=1000 width=168)
Output: _id, unit_id, "timestamp",  data
Sort Key: record."timestamp" DESC
->  Foreign Scan on unit.record  (cost=25.00..1025.00 rows=1000 width=168)
Output: _id, unit_id, "timestamp",  data
Foreign Namespace: XX-db.unit_records
Query Identifier: 2397074415654613066

The problem

The query is very slow, most likely the extension is fetching all the rows then order them in PostgresSQL.

Question

How can I implement ORDER BY push-down so that PostgreSQL receives just the first row? cc @jeevanchalke

Thank you!

vaibhavdalvi93 commented 2 years ago

Thanks, @iosifnicolae2 for raising an issue.

The ORDER BY and/or LIMIT/OFFSET push-downs are currently not supported. However, the same is on our roadmap and we already started working on it.

iosifnicolae2 commented 2 years ago

@vaibhavdalvi93 thank you for the reply.

How can I help? Do you happen to have a rough estimate on how much would it take to be pushed on the master branch?

Thanks!

PS. (off-topic) This extension would be very helpful for people currently using Hasura

jeevanchalke commented 2 years ago

We are planning to get both these pushdowns checked-in in the next quarter.

Thanks for using the extension. Your constant feedback and feature requests are all welcome.

iosifnicolae2 commented 2 years ago

Please let me know if you have any beta updates so I can give it a try.

Thanks!

iosifnicolae2 commented 2 years ago

Also, would it possible to push-down LIMIT operator? (we're querying a big database and unfortunetly the extension is not feasible for our use-case yet.. )

jeevanchalke commented 2 years ago

Yes. We are working on both the push-downs.

Note that, if the query has an ORDER BY clause as well as a LIMIT clause, then only LIMIT can't be pushed down if ORDER BY doesn't. As LIMIT's output depended on the sorted result-set. So if your query has both clauses, then essentially you need both these pushdowns. So we are working on them in parallel.