Wrong cost estimation for foreign tables join withuse_remote_estimate disabled

Hi hackers,

I hope that somebody understand postgres_fdw cost calculation magic 
better than I;)
The following very simple test reduce the problem with wrong cost 
estimation:


create table t1(x integer primary key, y integer);
create index on t1(y);
insert into t1 values (generate_series(1,1000000), 
generate_series(1000001,2000000));
create table t2(x integer primary key);
insert into t2 values (generate_series(1,1000000));
create server pg_fdw  FOREIGN DATA WRAPPER postgres_fdw options(host 
'localhost', dbname 'postgres');
create foreign table t1_fdw(x integer, y integer) server pg_fdw options 
(table_name 't1', use_remote_estimate 'false');
create foreign table t2_fdw(x integer) server pg_fdw options (table_name 
't2', use_remote_estimate 'false');
analyze t1;
analyze t2;
analyze t2_fdw;
analyze t1_fdw;
explain select * from t1_fdw join t2_fdw on t1_fdw.x=t2_fdw.x where y in 
(1234567,1234577,1234667,1235567,1244567,1334567);


-------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=22125.20..60300.26 rows=6 width=12) (actual 
time=439.187..1849.459 rows=6 loops=1)
    Hash Cond: (t2_fdw.x = t1_fdw.x)
    ->  Foreign Scan on t2_fdw  (cost=100.00..34525.00 rows=1000000 
width=4) (actual time=0.526..1711.671 rows=1000000 loops=1)
    ->  Hash  (cost=22025.12..22025.12 rows=6 width=8) (actual 
time=0.511..0.511 rows=6 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 9kB
          ->  Foreign Scan on t1_fdw  (cost=100.00..22025.12 rows=6 
width=8) (actual time=0.506..0.507 rows=6 loops=1)
  Planning Time: 0.173 ms
  Execution Time: 1849.871 ms
(8 rows)

So instead of pushing join to the remote server, optimizer decides that 
it is more efficient to perform join locally.
If IN lis contains less alternatives (<= 2), then correct plan is used:
postgres=# explain select * from t1_fdw join t2_fdw on t1_fdw.x=t2_fdw.x 
where y in (1234567,1234577);
                        QUERY PLAN
---------------------------------------------------------
  Foreign Scan  (cost=100.00..41450.04 rows=2 width=12)
    Relations: (public.t1_fdw) INNER JOIN (public.t2_fdw)
(2 rows)


It is possible to force Postgres to use correct plan by setting 
"fdw_startup_cost" to some very large value (100000000 for example).

Also correct plan is used when use_remote_estimate is true. But in this 
case query optimization time is too large (not at this dummy example, 
but on real database  and query with join of many large tables it takes 
about 10 seconds to perform remote estimation of all joined tables).

Please notice that optimizer correctly estimates number of retrieved 
rows: 6.
But it overestimates cost of remote join.
Looks like it is because of the following code in estimate_path_cost_size:

             /*
              * Run time cost includes:
              *
              * 1. Run time cost (total_cost - startup_cost) of 
relations being
              * joined
              *
              * 2. Run time cost of applying join clauses on the cross 
product
              * of the joining relations.
              *
              * 3. Run time cost of applying pushed down other clauses 
on the
              * result of join
              *
              * 4. Run time cost of applying nonpushable other clauses 
locally
              * on the result fetched from the foreign server.
              */
             run_cost = fpinfo_i->rel_total_cost - 
fpinfo_i->rel_startup_cost;
             run_cost += fpinfo_o->rel_total_cost - 
fpinfo_o->rel_startup_cost;
             run_cost += nrows * join_cost.per_tuple;
             nrows = clamp_row_est(nrows * fpinfo->joinclause_sel);
             run_cost += nrows * remote_conds_cost.per_tuple;
             run_cost += fpinfo->local_conds_cost.per_tuple * 
retrieved_rows;

815                run_cost = fpinfo_i->rel_total_cost - 
fpinfo_i->rel_startup_cost;
(gdb) p fpinfo_i->rel_total_cost
$23 = 14425
2816                run_cost += fpinfo_o->rel_total_cost - 
fpinfo_o->rel_startup_cost;
(gdb) p fpinfo_o->rel_total_cost
$25 = 21925
2817                run_cost += nrows * join_cost.per_tuple;
(gdb) p run_cost
$26 = 36350

I wonder if it is possible to make estimation of foreign join cost more 
precise.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company