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