FOREIGN DATA WRAPPER to provide access to external objects has been extended.
3.7.1 Sort Push-down
In previous versions sort processing for FOREIGN TABLE were executed on the local instance after data were transferred from the external system. In PostgreSQL 9.6 the ORDER BY clause can be sent to external objects. In the following example, the SELECT statement with the ORDER BY clause to the remote instance is executed using the postgres_fdw module.
Example 42Sort Push-down
postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM table1 ORDER BY 1 ;
QUERY PLAN
------------------------------------------------------------------------------
Foreign Scan on public.table1 (cost=100.00..139.87 rows=871 width=70)
(actual time=0.986..7109.985 rows=1000000 loops=1)
Output: c1, c2
Remote SQL: SELECT c1, c2 FROM public.table1 ORDER BY c1 ASC NULLS LAST
Planning time: 0.130 ms
Execution time: 7201.854 ms
(5 rows)
The example shows that the ORDER BY clause in SQL Statement for table1 which is a FOREIGN
TABLE is executed on the remote instance.
3.7.2 Direct Modify
DELETE and UPDATE statements in the old version for FOREIGN TABLE created cursors by SELECT FOR UPDATE statement, the update process was executed for each record in the cursor. In PostgreSQL 9.6 update DML can be executed directly in the remote instance. In the following example, the DELETE statement to FOREIGN TABLE is executed in PostgreSQL
9.5 and PostgreSQL 9.6. In PostgreSQL 9.5 SELECT FOR UPDATE statement is executed, but it
changes to DELETE statement in PostgreSQL 9.6.
PDF page 47
Example 43DELETE statement in PostgreSQL 9.5
postgres=> EXPLAIN (ANALYZE, VERBOSE) DELETE FROM data1 WHERE c1=100 ;
QUERY PLAN
------------------------------------------------------------------------------
Delete on public.data1 (cost=100.00..144.40 rows=14 width=6)
(actual time=582.328..582.328 rows=0 loops=1)
Remote SQL: DELETE FROM public.data1 WHERE ctid = $1
-> Foreign Scan on public.data1 (cost=100.00..144.40 rows=14 width=6)
(actual time=527.345..527.347 rows=1 loops=1)
Output: ctid
Remote SQL: SELECT ctid FROM public.data1 WHERE ((c1 = 100::numeric))
FOR UPDATE
Planning time: 0.746 ms
Execution time: 583.628 ms
(7 rows)
Example 44DELETE statement in PostgreSQL 9.6
postgres=> EXPLAIN (ANALYZE, VERBOSE) DELETE FROM data1 WHERE c1=100 ;
QUERY PLAN
------------------------------------------------------------------------------
Delete on public.data1 (cost=100.00..144.40 rows=14 width=6)
(actual time=1.019..1.019 rows=0 loops=1)
-> Foreign Delete on public.data1 (cost=100.00..144.40 rows=14 width=6)
(actual time=1.016..1.016 rows=1 loops=1)
Remote SQL: DELETE FROM public.data1 WHERE ((c1 = 100::numeric))
Planning time: 0.222 ms
Execution time: 1.414 ms
(5 rows)
3.7.3 Join Push-down
A process to join the tables on the same FOREIGN SERVER can now be performed on a remote instance.
PDF page 48
Example 45Join Push-down
postgres=> CREATE FOREIGN TABLE foreign1(c1 numeric, c2 varchar(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> CREATE FOREIGN TABLE foreign2(c1 numeric, c2 varchar(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=>
postgres=> EXPLAIN (ANALYZE, VERBOSE) SELECT COUNT(*) FROM
foreign1 f1, foreign2 f2 WHERE f1.c1 = f2.c1 AND f1.c1 = 100 ;
QUERY PLAN
----------------------------------------------------------------------------------
Aggregate (cost=35912.03..35912.04 rows=1 width=8)
(actual time=2.558..2.558 rows=1 loops=1)
Output: count(*)
-> Foreign Scan (cost=100.00..35912.03 rows=1 width=0)
(actual time=2.549..2.550 rows=1 loops=1)
Relations: (public.foreign1 f1) INNER JOIN (public.foreign2 f2)
Remote SQL: SELECT NULL FROM (public.foreign1 r1 INNER
JOIN public.foreign2 r2 ON (((r2.c1 = 100::numeric)) AND
((r1.c1 = 100::numeric))))
Planning time: 0.284 ms
Execution time: 3.822 ms
(7 rows)