News for hackers. Stuff that matters.
Re:Foreign data wrappers: PostgreSQL's secret weapon? by Shoreleave on Wednesday July 08, 2020 @12:55PM (#23771745)
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?

Re:Foreign data wrappers: PostgreSQL's secret weapon? by mildbyte on Wednesday July 08, 2020 @01:32PM (#23772110)
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...

Re:Foreign data wrappers: PostgreSQL's secret weapon? by brlewis on Wednesday July 08, 2020 @01:37PM (#23772180)
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...