search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
521 stars 160 forks source link

Support pushdown GROUP BY, HAVING, ORDER BY, LIMIT OFFSET #245

Closed MinhLA1410 closed 2 years ago

MinhLA1410 commented 2 years ago

Currently, we already implement to pushdown groupby, orderby, limit, offset. I would like to contribute them into repository and community. If you have interest? I will create pull request for share this feature.

Summary modification:

Reference testcase of postgres_fdw.sql in postgres_fdw:

EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: t1.*, c3, c1
   Remote query: SELECT `C 1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8` FROM `mysql_fdw_post`.`T 1` ORDER BY `c3` IS NULL ASC, `c3` ASC, `C 1` IS NULL ASC, `C 1` ASC LIMIT 10 OFFSET 100
(3 rows)
surajkharage19 commented 2 years ago

Thank you, @MinhLA1410, for showing up an interest in contributing to the mysql_fdw, it's really helpful.

However, we have already committed Aggregate Push down feature, which will push down the GROUP BY + HAVING queries to the MySQL side. And we are working on ORDER BY pushdown which is almost done and will be committed soon. We have also a patch ready for LIMIT/OFFSET pushdown but that is not yet reviewed and tested thoroughly.

Other features are all welcome. However, it would be good if we go one-by-one with them. So that it will be easy to review and test thoroughly.

Thanks, looking forward to the collaborative work to make the mysql_fdw feature reach and robust.

mapix commented 2 years ago

@MinhLA1410
We ran into a tricky situation where the datatable was full table scanned without limit pushdown, would you mind sharing your branch link here, I'll give it a try.

MinhLA1410 commented 2 years ago

Sorry for reply later. But I think this feature was released in: https://github.com/EnterpriseDB/mysql_fdw/releases/tag/REL-2_8_0 So, I will close the issue.