Postgres has two ways to estimate costs and row counts for postgres_fdw. When a query joins a foreign table, the local planner needs to know what that remote data looks like to pick a smart plan. postgres_fdw can get those numbers from two places. Option 1: Ask the remote server at planning time ``` ALTER SERVER remote OPTIONS (ADD use_remote_estimate 'true'); -- Or ALTER FOREIGN TABLE orders OPTIONS (ADD use_remote_estimate 'true'); ``` With this enabled, postgres_fdw sends remote EXPLAIN queries over the wire during the planning phase and adopts the remote planner's estimates. Because those estimates leverage the remote server's live statistics and indexes, the local planner discovers what the remote database can do efficiently. For instance, if you join 50 local customer IDs to a remote orders table with 1,000,000 rows and an index on customer_id, use_remote_estimate lets Postgres choose a parameterized nested loop. The remote query becomes: ``` SELECT id, total FROM orders WHERE customer_id = $1 ``` Instead of pulling 1,000,000 rows across the network into a local hash join, it executes 50 fast index lookups on the remote side. The tradeoff is planning overhead. It pays a network round-trip for a remote EXPLAIN (often 2–3 round trips per foreign table per query). Option 2: Pre-sample and store statistics locally ``` ANALYZE orders; ``` Running ANALYZE on a foreign table samples the remote table over the network and saves the column statistics locally in pg_statistic. Future query planning requires zero remote round-trips. The gotchas: • Autovacuum ignores foreign tables. It will never automatically analyze them, so put an explicit ANALYZE on a schedule (e.g., using pg_cron). • Local stats describe rows, not remote indexes. Local statistics tell the planner how many rows match a filter, but not that an index exists remotely to fetch them cheaply. For indexed parameterized lookups, use use_remote_estimate. For broad scans and analytical queries, local statistics are usually enough.

Postgres has the concept of infinity. When dealing with numbers, dates, or timestamps, it can be a clean, effective tool for schema design. In our post on the Ambiguity of NULL in Postgres, we explained how calculations or comparisons with NULL create a contagion, which causes the result of the expression to always yield NULL. While NULL represents an unknown or missing value, infinity represents a known, unbounded value that is larger than all finite numbers or dates (and -infinity for values smaller than all others). Postgres range types (daterange, tsrange) already handle open-ended bounds natively. But if you are using separate columns for unbounded start/end dates or values, consider using infinity instead of NULL. The "NULL Contagion" Problem The following is what we mean by "contagion". When using the greater than operator to compare a date with NULL, the value is NULL: ```sql SELECT DATE '2024-06-15' > NULL AS gt_null; -- (null) ``` Because a WHERE clause only keeps rows where the condition evaluates to TRUE, any row containing a NULL comparison gets dropped. The standard workaround is padding every query with `OR ending_date IS NULL`. If your schema uses 'infinity' to represent an open-ended date, you eliminate the need for those query workarounds. Using Infinity in Practice Here is a price history table that stores the open-ended row as `'infinity'::date`. The greater-than comparison returns true against it, because infinity is larger than every finite date: ```sql CREATE TABLE prices ( product_id int NOT NULL, price numeric(10,2) NOT NULL, valid_from date NOT NULL, valid_to date NOT NULL ); INSERT INTO prices VALUES (1, 9.99, DATE '2024-01-01', DATE '2024-04-01'), (1, 11.99, DATE '2024-04-01', DATE '2024-07-01'), (1, 14.99, DATE '2024-07-01', 'infinity'::date); SELECT price FROM prices WHERE product_id = 1 AND valid_from <= DATE '2099-12-31' AND valid_to > DATE '2099-12-31'; -- 14.99 ``` Key Behaviors to Keep in Mind • Sorting: infinity sorts logically as greater than any finite value (and -infinity as smaller). NULL will still sort higher than infinity unless you use NULLS last. • Indexing & Performance: Standard B-Tree indexes index 'infinity' natively. Queries filtering on ranges (e.g., valid_to > CURRENT_DATE) can utilize clean single-index scans without forcing complex OR clauses or bitmap index scans to capture NULL values. • MIN / MAX Aggregates: max() encountering 'infinity' returns 'infinity'. min() will evaluate finite values normally unless '-infinity' is present. • Arithmetic: Adding or subtracting days from 'infinity' still yields 'infinity'. Functions like date_trunc() preserve infinity. When using a function against infinity, ensure it behaves as expected. • Equality & Constraints: Unlike NULL, infinity is equal to infinity ('infinity'::date = 'infinity'::date is TRUE). This allows UNIQUE constraints and indexes to evaluate open-ended rows predictably. By setting NOT NULL, but allowing infinity on a column, you remove the headache double checking every query for a "OR ... IS NULL". An asside about Toy Story: While writing this, I realized "To Infinity and Beyond" was a essential to the irrational confidence of the character of Buzz Lightyear. There is no beyond infinity, but he doesn't care, he's going to pursue it anyhow.