I'm using Postgres FDW at my current work and, while it has its advantages and use cases, JOIN operations can be terribly slow. Also, good luck (not) working with remote sequences.
I’ve implemented a (relatively simple) FDW myself. Performance largely depends on the operations you can push down to the native store. In some cases (e.g. SQLite and remote Postgres) that includes not just selections but even joins.
There's some query planner tweaks you can use to speed up JOINs with FDWs [0]. In layered querying [1], we had an issue with the planner choosing nested loop joins (which essentially run as multiple small single-row fetches) which tank performance if starting a scan has a large latency overhead. This can happen if the FDW underreports its startup cost.
If you use `SET enable_nestloop=off`, this will disable them for that session and use alternative strategies (like hash or merge join) which might be faster.