When dealing with foreign tables, I believe planning is not the same because of access to statistics (maybe has improved since 9.6 though). I just wonder... Would it be a viable option to create a materialized view using the FDW but then use the PHP script against the local tables only? Materialized views are not maintained automatically, but you have local statistics and can create indexes. Just a thought in case the data is not changing constantly and this might fit the need.
Also, it seems like perhaps the foreign queries might be more targeted if some data was encouraged to be pre-computed. What would be the expected row count from just table1?
with cte_interesting_t1_rows_precomputed AS materialized(
select
select t1.aaaa as maindb_aaaa, t1.bbb as maindb_bbb, t1.ccccc as maindb_ccccc, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.ddddddddd as maindb_ddddddddd, null::text as db1_sth,
t2.eeeeeeee as db1_eeeeeeee, t2.ffffffff as db1_ffffffff, null::text as db2_sth,
t3.eeeeeeee as db2_eeeeeeee, t3.ffffffff as db2_ffffffff
from cte_interesting_t1_rows_precomputed AS t1
left join database1_fdw.table2 AS t2 on t1.aaaa = t2.btatpd_aaaa and t2.btatpd_fecha = '20220119120000' AND needs_t2
left join database2_fdw.table2 AS t3 on t1.aaaa = t3.btatpd_aaaa and t3.btatpd_fecha = '20220119120000' AND needs_t3
where
Also, it seems like perhaps the foreign queries might be more targeted if some data was encouraged to be pre-computed. What would be the expected row count from just table1?
Note- your explain plan doesn't mention "fichero_origen" field name. Is that just a copy/paste error?
with cte_interesting_t1_rows_precomputed AS materialized(
select
t1.*,
substring(t1.bbb from 1 for 3) in (<some_values>) AND t1.file = 'file_name.csv' AS needs_t2,
substring(t1.bbb from 1 for 3) in (<some_different_values>) AS needs_t3
FROM
table1 t1
where t1.ccccc = 'ACTIVE'
and t1.fichero_origen = 'file_name.csv'
)
and t1.fichero_origen = 'file_name.csv'
)
select t1.aaaa as maindb_aaaa, t1.bbb as maindb_bbb, t1.ccccc as maindb_ccccc, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.ddddddddd as maindb_ddddddddd, null::text as db1_sth,
t2.eeeeeeee as db1_eeeeeeee, t2.ffffffff as db1_ffffffff, null::text as db2_sth,
t3.eeeeeeee as db2_eeeeeeee, t3.ffffffff as db2_ffffffff
from cte_interesting_t1_rows_precomputed AS t1
left join database1_fdw.table2 AS t2 on t1.aaaa = t2.btatpd_aaaa and t2.btatpd_fecha = '20220119120000' AND needs_t2
left join database2_fdw.table2 AS t3 on t1.aaaa = t3.btatpd_aaaa and t3.btatpd_fecha = '20220119120000' AND needs_t3
where
(t2.eeeeeeee is null and t3.eeeeeeee is null)
or
(t2.eeeeeeee is not null and t1.ddddddddd <> t2.ffffffff)
or
(t3.eeeeeeee is not null and t1.ddddddddd <> t3.ffffffff)
order by t1.bbb nulls last;
(t2.eeeeeeee is not null and t1.ddddddddd <> t2.ffffffff)
or
(t3.eeeeeeee is not null and t1.ddddddddd <> t3.ffffffff)
order by t1.bbb nulls last;
