PostgreSQL Foreign Tables: Speeding Up ORDER BY/LIMIT Pushdown

This short follow-up builds on the previous post about PostgreSQL foreign tables and shows a simple change that can make certain queries go from painfully slow to instant.

First, a quick reminder of how to set up a foreign table:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
 
CREATE SERVER markets_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');
 
CREATE USER MAPPING FOR current_user SERVER markets_server OPTIONS (user 'remote_user', password 'remote_password');

-- prices.kraken is local_schema.local_table
CREATE FOREIGN TABLE prices.kraken (
  "period" int4 NOT NULL,
  base varchar NOT NULL,
  "quote" varchar NOT NULL,
  "timestamp" int8 NOT NULL,
  o float8 NULL,
  h float8 NULL,
  l float8 NULL,
  c float8 NULL,
  v float8 NULL,
  cc int4 NULL
 ) SERVER markets_server OPTIONS (schema_name 'remote_schema', table_name 'remote_table');

We would expect that transferring large amounts of data over FDW would be slow, but for smaller datasets I expect speed to be nearly comparable to local queries, depending on network latency.

Take this query for example:

SELECT c
FROM kraken
WHERE base='BTC' AND quote='USD' AND timestamp<=1513622125
ORDER BY timestamp DESC
LIMIT 1;

It returns almost instantly if it’s run on a local Postgres instance. Running the same query on a remote Postgres instance over a local network took ~30s.

Let’s look at why. Running this on the remote server:

EXPLAIN (VERBOSE) SELECT c
FROM kraken
WHERE base='BTC' AND quote='USD' AND timestamp<=1513622125
ORDER BY timestamp DESC
LIMIT 1;

One part of the output: Remote SQL: SELECT "timestamp", c FROM public.kraken WHERE (("timestamp" <= 1513622125)) AND ((base = 'BTC')) AND ((quote = 'USD'))

This means the WHERE clause is being sent to the remote server (good), but the ORDER BY and LIMIT are not. The remote sends back all matching rows, and the local server sorts and limits — very inefficient over the network.

The fix is to let the planner use row estimates from the remote side so it can choose a plan that pushes down ORDER BY/LIMIT:

ALTER SERVER markets_server OPTIONS (add use_remote_estimate 'true');

Now the EXPLAIN (VERBOSE) shows: Remote SQL: SELECT "timestamp", c FROM public.kraken WHERE (("timestamp" <= 1513622125)) AND ((base = 'BTC')) AND ((quote = 'USD')) ORDER BY "timestamp" DESC NULLS FIRST LIMIT 1::bigint

The same query now runs instantly.

Why this works:

• Without use_remote_estimate, the planner relies on local stats for the foreign table and may prefer to sort locally.
• With it enabled, PostgreSQL asks the remote server for cost/row estimates and often realizes that pushing down ORDER BY/LIMIT is cheaper.

Additional tuning tips:

• Index the remote table to support your filter and order: for the example above, an index like (base, quote, timestamp DESC) (or (base, quote, timestamp) with a DESC scan) helps the remote side return the single latest row quickly.
• Consider collecting statistics locally: ANALYZE prices.kraken; — postgres_fdw can sample the remote table to improve local estimates when use_remote_estimate is off.
• Control batch size if you do need to pull many rows: ALTER SERVER markets_server OPTIONS (set fetch_size '10000');
• Remember that use_remote_estimate adds planning-time overhead (extra round trips to the remote). For simple queries it’s negligible; for complex queries it can be worth it if it improves pushdown.

Quick checklist

• Enable the extension and create the server/user mapping
• Create the foreign table (or IMPORT FOREIGN SCHEMA)
• Ensure helpful indexes exist on the remote table
• Turn on use_remote_estimate for better pushdown when needed
• Verify with EXPLAIN (VERBOSE) that ORDER BY/LIMIT (and other operations) are pushed down

Summary

If your FDW query is fast locally but slow over the network, check whether ORDER BY/LIMIT is pushed down. Flipping on use_remote_estimate can be all it takes to move the sort and limit to the remote side and restore “instant” performance.