Re: Query much slower from php than psql or dbeaver

El vie, 21 ene 2022 a las 5:04, Michael Lewis (<mlewis(at)entrata(dot)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.