Hacker News Reader

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?

View on HN

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