El vie, 21 ene 2022 a las 5:04, Michael Lewis (<mle...@entrata.com>) escribió:
> 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? > > Note- your explain plan doesn't mention "fichero_origen" field name. Is > that just a copy/paste error? > It's a column renaming mistake. t1.fichero_origen = t1.file (it's the column where incoming file name is stored). > > 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' > ) > > 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; > I'm doing some tests after reading carefully your answer and Tom's, and I'll post back. Thank you both.
