Simple query is much slower than directly executed against the mysql server

[pietrobaricco]

I understand that limit and offset are not pushed down to mysql, so using tables larger than a few thousand rows is impractical and requires the use of matviews on the postgres side to achieve reasonable performance. But what puzzles me is that I have a table with ~20k rows, let's call it users

A trivial query such as

select id from users

returns the whole resultset in less than 500ms when executed directly from mysql, yet it takes 10 seconds when using a fdw.

Foreign Scan on users  (cost=25.00..1025.00 rows=1000 width=90) (actual time=118.242..9399.559 rows=16339 loops=1)
  Remote server startup cost: 25
Planning Time: 95.021 ms
Execution Time: 9519.404 ms

I'm using postgres 13 and google cloud sql with its proxy, same connection parameters for both fdw and mysql client.

[matthewwo]

I observed the same thing. It seems like this happens for a table with a lot of rows. I have a table with >700k rows, and it takes around 3 seconds to run this simple query: select * from xxx limit 1 while doing the same on a smaller table with >10k rows only takes 500ms.

[surajkharage19]

Hi @prankymat and @pietrobaricco,

We have now added LIMIT OFFSET pushdown support along with other features for performance benefit. Can you please re-check at your end if that is useful for you? Kindly share your feedback.

[pietrobaricco]

Hi, I tried today with a fresh build and indeed, the situation is now much better. Thank you!

[surajkharage19]

Glad to hear that the pushdown features are helpful at your end.

We will close the issue from our end. Feel free to open a new issue if you face any further issues on this.

[jeevanchalke]

Closing per comments above.