With PostgreSQL's postgres_fdw, how do you see which parts of a query run on the remote server, and what stops a WHERE clause from being pushed down?
answer
- look at what was actually sent
- EXPLAIN VERBOSE on the foreign scan
- built-in and immutable only
- now() is not immutable
- same server, same user mapping
basics
~20 sEXPLAIN VERBOSE shows each foreign scan's Remote SQL. postgres_fdw ships a WHERE clause only when its functions, operators and types are built-in or from a listed extension, immutable and collation-safe; otherwise rows cross the network and are filtered locally.
solid answer
~40 s`EXPLAIN VERBOSE` prints a `Remote SQL:` line for every `Foreign Scan`: the exact statement sent to the remote server. A `Filter:` line on that same node is work done locally after the rows arrived. postgres_fdw sends a clause only when every function, operator and type in it is built-in or belongs to an extension named in the server's `extensions` option, every function is `IMMUTABLE`, and its collations are safe. So `paid_at >= now() - interval '1 day'` stays local, because `now()` is only `STABLE`, and the whole table crosses the wire; a literal or bind parameter computed by the client ships. Joins are pushed only between foreign tables on the same server under the same user mapping, and an aggregate is pushed only when no condition beneath it had to stay local.
code
console · 18 linesfinance=> EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, sum(amount) FROM billing_remote.payments
WHERE paid_at >= now() - interval '1 day' GROUP BY customer_id;
HashAggregate
Output: customer_id, sum(amount)
Group Key: payments.customer_id
-> Foreign Scan on billing_remote.payments
Output: customer_id, amount, paid_at
Filter: (payments.paid_at >= (now() - '1 day'::interval))
Remote SQL: SELECT customer_id, amount, paid_at FROM billing.payments
finance=> EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, sum(amount) FROM billing_remote.payments
WHERE paid_at >= '2026-10-04 00:00+00' GROUP BY customer_id;
Foreign Scan
Output: customer_id, (sum(amount))
Relations: Aggregate on (billing_remote.payments)
Remote SQL: SELECT customer_id, sum(amount) FROM billing.payments WHERE ((paid_at >= '2026-10-04 00:00:00+00'::timestamp with time zone)) GROUP BY 1go deeper
Recall that postgres_fdw tries to send filters to the remote server and that EXPLAIN VERBOSE shows the Remote SQL it actually sent.
Explain the rules: built-in or listed-extension objects only, immutable functions only, safe collations, and joins only within one server and one user mapping.
Diagnose a slow cross-server report from its plan: spot the local Filter, trace why the aggregate stayed local, and rewrite the clause so the remote server does the work.
Judge when pushdown limits make a live foreign query the wrong design, and when moving the computation next to the data, or the data next to the computation, is cheaper.
## What pushdown means for postgres_fdw `postgres_fdw` builds a SQL statement for the remote PostgreSQL server out of the part of your query it can prove is safe to run there. Everything else runs locally on rows that have already crossed the network. **Pushdown** is the difference between the billing server returning 40 aggregated rows to the finance database and it streaming 30 million payment rows for the finance database to aggregate. What postgres_fdw can send: - **`WHERE` clauses** on a foreign table, and only the columns the query needs. - **Joins** between foreign tables on the same foreign server, shown as `Relations: (a) INNER JOIN (b)`. - **Semi-joins** from `EXISTS` and `IN` subqueries, pushed since PostgreSQL 17. - **Aggregates** with `GROUP BY` and `HAVING`, shown as `Relations: Aggregate on (...)`. - **`ORDER BY` and `LIMIT`**, when the rows beneath are fully remote. - **Whole `UPDATE` and `DELETE` statements**, shown as a `Foreign Update` or `Foreign Delete` node. ## The shippability rules A clause, join condition or aggregate expression is sent only when all of these hold: 1. **Every type, operator and function is built-in**, or belongs to an extension listed in the server's `extensions` option. Listing an extension is your promise that it is installed, in a compatible version, on both servers. 2. **Every function and operator is `IMMUTABLE`.** The source comment gives the reason: sending `now()` to the remote side could cause confusion from clock offsets. So `now()`, `current_date` and `clock_timestamp()` keep a clause local. 3. **Collations are safe**: a comparison whose collation does not come from a foreign column (or the default) stays local, because the two servers might sort text differently. 4. **For a join**, both tables are on the same foreign server and use the same user mapping, and the planner judges one remote join cheaper than fetching each side. 5. **For an aggregate**, no condition on the underlying scan may be local, because those rows must be filtered before they are grouped. Rule 5 makes one non-shippable `WHERE` clause expensive twice over: the filter runs locally, and so does the aggregation above it. ## Reading the evidence You do not need to reason from the rules; `EXPLAIN VERBOSE` shows the result. On each `Foreign Scan`: - **`Remote SQL:`** is the statement the remote server receives. Look for your `WHERE`, `GROUP BY` and `ORDER BY` in it. - **`Filter:`** lists conditions applied locally, after transfer. - **`Relations:`** shows what was folded into the scan, such as a join or an aggregate. For `UPDATE` and `DELETE`, a `Foreign Update` node with a remote `UPDATE ...` means one statement did the job. Otherwise postgres_fdw fetches the target rows with `SELECT ... FOR UPDATE` and then sends `UPDATE ... WHERE ctid = $1` once per row, one round trip each. That fallback happens when a `WHERE` clause cannot be shipped, the statement has local joins, the target has local row-level `BEFORE`/`AFTER` triggers or stored generated columns, or a parent view adds `CHECK OPTION`. Reading plan trees in general belongs to EXPLAIN itself; here the point is the three lines above. ## Fixing a clause that stays local | Cause | Example | Fix | |---|---|---| | Function not immutable | `paid_at >= now() - interval '1 day'` | compute the cut-off in the client and pass a literal or bind parameter | | Function from an extension | an `IMMUTABLE` function from an extension installed on both sides | add the extension to the server's `extensions` option | | Collation mismatch | a column declared with a different collation from the remote one | declare the foreign column with the remote collation | | Built-in newer than the remote server | a function added in a newer release, run against an older remote | wrap the foreign table in a sub-`SELECT` with `OFFSET 0` and apply the function outside | The last row comes from the manual: postgres_fdw assumes immutable built-ins exist remotely, which fails when the remote server is older. ## Why it matters A clause that stays local does not just cost CPU. Every row it rejects was first read by the remote server, sent over the network in batches of `fetch_size` rows, and parsed locally. On a finance report that runs nightly against a large billing table, the gap between a pushed and an unpushed filter can be the gap between seconds and an hour.
- What does the postgres_fdw server option extensions do, and what is the risk?It lists extensions installed in compatible versions on both servers, so their `IMMUTABLE` functions and operators become shippable. It is a promise postgres_fdw does not verify: if the remote copy is missing or behaves differently, remote queries fail or, worse, return different results. It can only be set on the server, not per table.
- When is an UPDATE on a postgres_fdw foreign table sent as one remote statement, and what happens otherwise?It is sent whole, as a `Foreign Update`, when every `WHERE` clause ships, there are no local joins, no local row-level triggers or stored generated columns on the target, and no `CHECK OPTION`; `SET` expressions must be built-in and immutable. Otherwise rows are fetched with `FOR UPDATE` and updated one by one with `WHERE ctid = $1`, a round trip per row.
- A newer built-in function in your WHERE clause is pushed to an older remote server and fails. What do you do?postgres_fdw assumes immutable built-ins exist remotely, so the remote server reports that the function does not exist. Rewrite the query so the foreign table sits in a sub-`SELECT` with `OFFSET 0`, which acts as an optimization fence, and apply the function outside it; the filter then runs locally.
Ordering from a warehouse by phone: if you describe what you want in terms the clerk understands, only those boxes are shipped. If your criterion depends on something only you have, like the time on your own wall clock, the clerk ships everything and you sort it at your door.
- postgres_fdw always fetches the whole remote table and filters locally.
- Any IMMUTABLE user-defined function is shipped to the remote server automatically.
- A filter on now() is pushed down because both servers keep the same time.
- A join between foreign tables on two different servers runs on one of them.
- Aggregates are pushed down even when a WHERE condition beneath them runs locally.
- Clauses are only pushed down when use_remote_estimate is turned on.
Assumes PostgreSQL 18; postgres_fdw has pushed EXISTS and IN semi-joins to the remote server since PostgreSQL 17.