For this recipe, we will continue to use the pg-primary and pg-report database servers. All queries should be performed by the postgres user in the pgbench database. Follow these steps to enforce better remote JOIN performance:
- Create a view for the basis of the join on pg-primary:
CREATE OR REPLACE VIEW v_pgbench_accounts_self_join AS SELECT a1.aid, a2.bid, a2.abalance FROM pgbench_accounts a1 JOIN pgbench_accounts a2 USING (aid) ORDER BY a1.aid DESC;
- Grant access to bench_user on the new view on pg-primary:
GRANT SELECT ON v_pgbench_accounts_self_join TO bench_user;
- Create a foreign table that references the view on pg-report:
CREATE FOREIGN TABLE pgbench_accounts_self ( aid INTEGER NOT NULL, bid INTEGER, ...