PostgreSQL 9.3 postgres_fdw: a Test Drive

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:

• Can I connect against different versions of PostgreSQL? Yes to test I tried connecting from my PostgreSQL 9.3 dev instance to my 9.2 instance on another box.
• Can I use a foreign type like -- you guessed it PostGIS if we have same version of PostGIS installed on both databases? Yes. I didn't try with different versions so not sure if that would work especially since the structure changed a bit between 1.5 and 2.0. I suspect 2.0 and 2.1 would work fine and might be a good way to cheat run 2.1 on 9.3 but creating derivatives of my 2.0 data with functions only available in 2.1.
• Will it use my spatial index? No or at least we couldn't figure out how and I don't see how it is possible, but would be nice if it could be
• Can I have a table defined with dynamic SQL? Nope or at least the docs don't mention it. This is a feature I really liked about the ODBC_FDW because often times I want the table to be filtered especially if I need my filter to use an expensive index like a spatial one or utilize functions only available on the foreign server. If that were part of the definition of the foreign table, then it could in theory process that part on the foreign server. So this makes postgres_fdw not a good replacement for dblink in many cases.

So here are the tests we did to experiment with:

The 9.3 server

1. 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

2. 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.

3. Install both extensions on a new PostgreSQL 9.3 database

   CREATE EXTENSION postgres_fdw;
    CREATE EXTENSION postgis VERSION "2.1.0SVN";

Setting up the Foreign Server, Users, Tables

For this test I connected to a tiger database I use for geocoding that has PostGIS 2.1.0SVN installed on it.

1. 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;

2. On spanking new 9.3 desktop server:

   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.