Introduction PostgreSQL foreign data wrappers (FDW) enable querying remote databases as if they were local tables. However, cross-database joins often suffer from severe performance issues because the query planner cannot accurately estimate remote row counts, and operations that should be pushed down to the remote server are executed locally instead.

Symptoms - Queries joining local and foreign tables take 10-100x longer than equivalent local joins - Network traffic spikes when running FDW queries (entire remote table fetched) - `EXPLAIN ANALYZE` shows `Foreign Scan` with no pushed-down conditions - Remote server shows full table scans triggered by local query - Join operations on foreign tables with WHERE clauses still fetch all rows

Common Causes - Remote statistics not collected, leading to inaccurate cost estimates - WHERE clauses and JOIN conditions not pushed down to the remote server - `use_remote_estimate` option not enabled on the foreign server - Complex expressions in WHERE clauses preventing pushdown optimization - Missing indexes on the remote table columns used in join conditions

Step-by-Step Fix 1. **Analyze the query plan to identify missing pushdown**: ```sql EXPLAIN (ANALYZE, VERBOSE) SELECT l.order_id, l.total, r.customer_name FROM local_orders l JOIN remote_customers r ON l.customer_id = r.id WHERE l.created_at > '2026-01-01' AND r.status = 'active'; -- Check for "Remote SQL:" section to see what was pushed down ```

  1. 1.Enable remote estimation on the foreign server:
  2. 2.```sql
  3. 3.ALTER SERVER remote_server OPTIONS (use_remote_estimate 'true');
  4. 4.`
  5. 5.Collect statistics on the foreign table:
  6. 6.```sql
  7. 7.ANALYZE remote_customers;
  8. 8.-- Check statistics
  9. 9.SELECT attname, null_frac, n_distinct, most_common_vals
  10. 10.FROM pg_stats WHERE tablename = 'remote_customers';
  11. 11.`
  12. 12.Create a materialized view to cache frequently accessed foreign data:
  13. 13.```sql
  14. 14.CREATE MATERIALIZED VIEW mv_active_customers AS
  15. 15.SELECT id, customer_name, email, status
  16. 16.FROM remote_customers
  17. 17.WHERE status = 'active';

CREATE INDEX idx_mv_active_customers_id ON mv_active_customers (id);

-- Refresh periodically REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_customers; ```

  1. 1.Restructure the query to maximize pushdown:
  2. 2.```sql
  3. 3.-- Instead of joining directly, pre-filter the remote side
  4. 4.WITH filtered_remote AS (
  5. 5.SELECT id, customer_name
  6. 6.FROM remote_customers
  7. 7.WHERE status = 'active'
  8. 8.)
  9. 9.SELECT l.order_id, l.total, f.customer_name
  10. 10.FROM local_orders l
  11. 11.JOIN filtered_remote f ON l.customer_id = f.id
  12. 12.WHERE l.created_at > '2026-01-01';
  13. 13.`
  14. 14.Use dblink for explicit remote query control when FDW pushdown fails:
  15. 15.```sql
  16. 16.SELECT *
  17. 17.FROM local_orders l
  18. 18.JOIN dblink(
  19. 19.'host=remote_db dbname=sales',
  20. 20.$$SELECT id, customer_name FROM customers WHERE status = 'active'$$
  21. 21.) AS r(id INT, customer_name TEXT) ON l.customer_id = r.id
  22. 22.WHERE l.created_at > '2026-01-01';
  23. 23.`

Prevention - Regularly run `ANALYZE` on foreign tables to update local statistics - Keep foreign server schemas in sync with local expectations - Use materialized views for frequently joined foreign data - Monitor network traffic for FDW queries to detect full-table fetches - Consider ETL replication instead of FDW for frequently queried remote data - Use `postgres_fdw` over `dblink` when possible as it has better pushdown support - Test FDW queries with production data volumes in staging before deployment