[Shoreleave]

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?

[mildbyte]

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...

[brlewis]

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...