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@xxxxxxxxxxxxx>) escribió: > > =?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald.valcarcel@xxxxxxxxx> 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
