# Pushing Spatial Predicates Down with PostGIS FDW

A federated join either pushes its filter into each domain or pulls each domain’s data across the network — and the difference is not a matter of degree. Pushdown turns a cross-domain join into two indexed local scans and a small final join; its absence turns the same query into a full table transfer that scales with the size of both datasets. The maddening part is that both produce correct results, so the failure is invisible until the query is slow enough to notice. This guide configures postgres_fdw so spatial predicates push down reliably, proves it from the plan, and shows the specific mistakes that silently disable it. It implements the pushdown discipline in Federated Query and Cross-Domain Spatial Joins within Federated Ownership & Routing Architecture.

# Prerequisites

# Step-by-Step Implementation

# 1. Create the server and make PostGIS operators shippable

The single most important setting is extensions: without it the planner assumes PostGIS operators are unknown to the remote side and refuses to ship any predicate that uses them.

-- fdw_setup.sql — a foreign server whose PostGIS predicates actually push down.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER hydrology_domain
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host 'hydrology-db.internal',
    port '5432',
    dbname 'hydrology',
    -- THE load-bearing option. Without it the planner treats every PostGIS
    -- operator as unshippable and pulls the whole table before filtering.
    extensions 'postgis',
    fetch_size '50000',              -- larger batches; the default 100 is far too small
    async_capable 'true'             -- lets two domains scan concurrently
  );

CREATE USER MAPPING FOR CURRENT_USER
  SERVER hydrology_domain
  OPTIONS (user 'federated_reader', password_required 'true');

IMPORT FOREIGN SCHEMA published
  LIMIT TO (flood_extents)
  FROM SERVER hydrology_domain
  INTO remote_hydrology;

Verify that PostGIS is declared shippable, which is not visible anywhere except the server options:

psql "$LOCAL_DSN" -c "SELECT srvname, srvoptions FROM pg_foreign_server;" \
  | grep -q 'extensions=postgis' && echo "PostGIS operators are shippable"

# 2. Analyze the foreign table so the planner can cost it

An un-analyzed foreign table defaults to a tiny row estimate, which makes the planner choose a nested loop that fetches the remote table repeatedly.

-- Sampling a foreign table costs a scan; do it after import and on a schedule.
ANALYZE remote_hydrology.flood_extents;

-- Confirm the estimate is realistic rather than the default placeholder.
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname = 'flood_extents';

Verify the estimate is within an order of magnitude of the truth; a wildly low estimate is the classic cause of a plan that looks fine and runs for an hour:

psql "$LOCAL_DSN" -c "SELECT count(*) FROM remote_hydrology.flood_extents;"

# 3. Write the join so the predicate is shippable

Pushdown is defeated by anything the remote side cannot evaluate — a local function, a mismatched type, a reprojection applied to the remote column.

-- federated_join.sql — a shippable predicate, both sides filtered remotely.
-- Parameters: :extent (WKT, EPSG:4326)
WITH bounds AS (
    -- The literal is evaluated locally and shipped as a constant. Correct.
    SELECT ST_GeomFromText(:'extent', 4326) AS g
)
SELECT p.parcel_id, h.severity,
       ST_Area(ST_Intersection(p.geom, h.geom)::geography) AS affected_m2
FROM local_cadastral.parcels p
JOIN remote_hydrology.flood_extents h
  -- && is the index-backed bounding-box operator and ships cleanly.
  ON p.geom && h.geom
 AND ST_Intersects(p.geom, h.geom)
CROSS JOIN bounds b
WHERE p.geom && b.g                -- pushes to the local index
  AND h.geom && b.g;               -- ships to the remote side as a constant predicate

Verify from the plan that the remote SQL carries the filter. This is the check that matters and the one most often skipped:

psql "$LOCAL_DSN" -c "EXPLAIN (ANALYZE, VERBOSE) $(cat federated_join.sql)" \
  | grep -A3 'Foreign Scan'
# The "Remote SQL" line MUST contain the geometry predicate. If it reads
# "SELECT ... FROM published.flood_extents" with no WHERE, nothing is pushed down.

# 4. Compare the two plans to quantify what pushdown buys

# Deliberately defeat pushdown by wrapping the remote column in a function, and
# compare. The difference is the argument for everything above.
psql "$LOCAL_DSN" <<'SQL'
\timing on
-- Shippable: the filter runs in the hydrology domain.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM remote_hydrology.flood_extents h
WHERE h.geom && ST_GeomFromText('POLYGON((-1 50, 1 50, 1 52, -1 52, -1 50))', 4326);

-- Not shippable: ST_Transform on the remote column forces a full fetch.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM remote_hydrology.flood_extents h
WHERE ST_Transform(h.geom, 3857) && ST_Transform(
  ST_GeomFromText('POLYGON((-1 50, 1 50, 1 52, -1 52, -1 50))', 4326), 3857);
SQL

# Configuration Reference

# Common Failure Modes & Fixes

The plan shows a Foreign Scan with no WHERE in the remote SQL. Root cause: extensions 'postgis' missing from the server options. Fix: add it and re-plan; this single option is the difference between a filtered scan and a full table transfer.

Pushdown works for && and not for ST_Intersects. Root cause: expected and correct. The bounding-box operator is index-backed and cheap; the exact predicate is neither, and the planner is right to evaluate it locally on the survivors. Fix: nothing — write both, as the join above does, so the cheap one ships and the exact one filters what returns.

A query that pushed down last month no longer does. Root cause: someone added a reprojection or a function around the remote geometry column. Fix: reproject the constant into the remote column’s CRS instead; a literal transformed locally ships as a constant, a column transformed remotely does not.

The join returns zero rows and no error. Root cause: the two sides are in different SRIDs, so their coordinate ranges do not overlap. Fix: assert both sides’ SRID before joining; PostGIS raises on mismatched SRIDs for some operators and silently returns nothing for others.

The plan is good and the query is still slow. Root cause: fetch_size at the default of 100, so a large result crosses the network in thousands of round trips. Fix: raise it; 50,000 is reasonable for geometry-bearing rows.

# FAQ

# Related

• Federated Query and Cross-Domain Spatial Joins — the parent topic and the pushdown taxonomy
• Querying GeoParquet Across Domains with Trino — the engine-based alternative for broad joins
• Bounding Cross-Domain Join Cost with Query Budgets — refusing the joins that cannot be bounded