Hacker News Reader

Comment by dezzeus | Hacker News Reader

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.

View on HN

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.

I'm struggling with this at the moment. Any tips to make JOINs faster?

And a question for any postgres people: say I have two distinct databases, but now need to join across them. What's best practice here?

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.

[0] https://www.postgresql.org/docs/current/runtime-config-query...

[1] https://www.splitgraph.com/docs/large-datasets/layered-query...

As to the first question -- I haven't done it, but have you tried materialized views?

EDIT: You've probably already tried that if you're following the docs, since they recommend it -- https://www.postgresql.org/docs/current/rules-materializedvi...

Reply on news.ycombinator.com