=?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