Hello Tom,
I've noticed something that may be a factor on this problem.
In the automated script, 5 or 6 seconds before this query happens, the
foreign table is created.
If I create the foreign table manually and skip its creation in the
automated script, and then I run the automated script,
the query takes 5-6 seconds to be executed instead of 2 hours.
Hope this info is helpful to dig further.
Thanks,
Eudald
El mié., 15 jul. 2020 a las 16:42, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
>
> =?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald.valcarcel@gmail.com> writes:
> > After running the query both manually and with the script, I've the
> > following logs:
>
> > MANUALLY:
> > Update on import_temp_2 tmp (cost=116.73..17352.10 rows=5557 width=293)
> > -> Hash Join (cost=116.73..17352.10 rows=5557 width=293)
> > Hash Cond: (lower((tmp.email)::text) = lower((bl.value)::text))
> > -> Seq Scan on import_temp_2 tmp (cost=0.00..14864.20
> > rows=370496 width=193)
> > Filter: (status = 1)
> > -> Hash (cost=116.70..116.70 rows=3 width=130)
> > Buckets: 32768 (originally 1024) Batches: 2
> > (originally 1) Memory Usage: 3841kB
> > -> Foreign Scan on blacklist_central bl
> > (cost=100.00..116.70 rows=3 width=130)
>
> > AUTOMATED:
> > Update on import_temp_2 tmp (cost=100.00..13295.86 rows=15 width=500)
> > -> Nested Loop (cost=100.00..13295.86 rows=15 width=500)
> > Join Filter: (lower((tmp.email)::text) = lower((bl.value)::text))
> > -> Seq Scan on import_temp_2 tmp (cost=0.00..13118.74
> > rows=1007 width=400)
> > Filter: (status = 1)
> > -> Materialize (cost=100.00..116.71 rows=3 width=130)
> > -> Foreign Scan on blacklist_central bl
> > (cost=100.00..116.70 rows=3 width=130)
>
> So the question is why you are getting an estimate of 370496 import_temp_2
> rows with status = 1 in the first case, and only 1007 rows in the second.
>
> I suspect that the true number of rows is quite large, causing the
> nested-loop plan to run slowly. (Is the row estimate of 3 for the
> foreign scan anywhere near reality, either?)
>
> You may need to insert a manual ANALYZE in your automated process to
> ensure that import_temp_2 has up-to-date stats before you try to do
> this step. It seems somewhat likely that autovacuum takes care of
> that for you in the "manual" case, but its reaction time is too slow
> to fill the gap for the automated process.
>
> regards, tom lane