In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

Question

We use Postgres 9.3, we want to split a large database (evaluated final size will be > 100 T) on many servers. As our users already have a set of existing queries, we cannot use solutions like PL/Proxy. We would like to use the postgres_fdw contrib to externalize some part of the data.

To illustrate our problem, we created 2 foreign tables foreign_table_1 and foreign_table_2. As inheritance seems not available on foreign tables, we created an union view like this :

CREATE VIEW union_table (id, name)
  AS      ( SELECT id, name FROM foreign_table_1 where id BETWEEN 1 AND 100000
  UNION ALL SELECT id, name FROM foreign_table_2 where id > 100000
  ) ;

The problem is that Postgres does not push the constraints into the query plan. For instance here both foreign tables are scanned :

postgres=# explain analyze select * from union_table where id = 1234;
 Append  (cost=100.00..303.35 rows=5 width=8) (actual time=1.113..1.113 rows=0 loops=1)
   ->  Foreign Scan on master_table_1  (cost=100.00..154.82 rows=1 width=8) (actual time=0.567..0.567 rows=0 loops=1)
   ->  Foreign Scan on master_table_2  (cost=100.00..148.48 rows=4 width=8) (actual time=0.538..0.538 rows=0 loops=1)
 Total runtime: 1.876 ms
(4 rows)

• Is it possible to push the WHERE clause into the query plan ?
• Is it possible to use inheritance with postgres_fdw ?
• Is there any other way to obtain a sharded table with Postgres ?

Answer 1

If the view isn't pushing down the WHERE predicates, maybe a db function that dynamically creates the necessary query and only included the Foreign Tables (or shards) that are necessary?

If you need distributed Postgres, have you looked into Postgres-XC? http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki