Just recently saw Michael Paguier's article on the recent commit of postgres_fdw so I was excited to try this out for myself. Questions we wanted to answer/experience were:
So here are the tests we did to experiment with:
Download latest 9.3 snapshot and compile with the pgsql_fdw extension - all documented in Mike's short but very useful Postgres 9.3 feature highlight: postgres_fdw
Compile PostGIS - I'm using 2.1.0 SVN on both boxes though my 9.3 (compiled with mingw-w64 64-bit) is fresh and my 9.2 (Windows EDB provided PostgreSQL 9.2 64-bit) is a couple of weeks older version of 2.1.0. 2.1 is just too Cool that we even run it in production now.
Install both extensions on a new PostgreSQL 9.3 database
CREATE EXTENSION postgres_fdw; CREATE EXTENSION postgis VERSION "2.1.0SVN";
For this test I connected to a tiger database I use for geocoding that has PostGIS 2.1.0SVN installed on it.
I created an account on our production server called: tiger_guest and gave it these rights
GRANT USAGE ON SCHEMA tiger TO tiger_guest; GRANT SELECT ON ALL TABLES IN SCHEMA public TO tiger_guest; GRANT SELECT ON ALL TABLES IN SCHEMA tiger TO tiger_guest;
CREATE SERVER postgres92prod_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'prod_server', port '5432', dbname 'tiger'); CREATE USER MAPPING FOR public SERVER postgres92prod_server OPTIONS (user 'tiger_guest', password 'whatever'); --this is just a subset of columns of the prod table -- CREATE FOREIGN TABLE edges (gid int, statefp varchar(2), countyfp varchar(3) , tlid bigint, fullname varchar(100), the_geom geometry) SERVER postgres92prod_server OPTIONS (schema_name 'tiger', table_name 'edges');
To test some queries -- this one is really slow proving the spatial index is not used, the plan I suspect you can't fully rely on since it won't show the skipping inheritance tables etc which is done on prod server as needed:
'--run directly on main server takes 20ms, on 9.3 foreign table takes 370ms SELECT fullname FROM edges WHERE fullname > '' AND ST_DWithin(the_geom, ST_GeomFromText('POINT(-71.3 41.9)', 4269), 0.05) LIMIT 10;
However this query with a helper fast scan is faster. It only uses spatial index on main server so hard to tell if btree index can be used.
'--run directly on main server takes 20ms, on 9.3 foreign table takes 9 seconds (46ms) SELECT fullname FROM edges WHERE fullname LIKE 'B%' AND ST_DWithin(the_geom, ST_GeomFromText('POINT(-71.3 41.9)', 4269), 0.05) LIMIT 10;
All and all a promising start.