Re: Issue executing query from container

Hello Tom,
Thanks for your answer! I didn't know about this plugin and configured
postgresql with it.

After running the query both manually and with the script, I've the
following logs:

MANUALLY:
2020-07-15 00:56:08.735 CEST [20457] cefron(at)kontriki LOG: statement:
UPDATE import_temp_2 AS tmp SET status = 3 FROM blacklist_central bl
WHERE tmp.status = 1 AND lower(tmp.email) =
lower(bl.value) AND bl.type = 1
2020-07-15 00:56:09.495 CEST [20457] cefron(at)kontriki LOG: duration:
759.102 ms plan:
Query Text: UPDATE import_temp_2 AS tmp SET status = 3 FROM
blacklist_central bl
WHERE tmp.status = 1 AND lower(tmp.email) =
lower(bl.value) AND bl.type = 1
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:
2020-07-15 01:01:27.336 CEST [22783] cefron(at)kontriki LOG: duration: 0.049 ms
2020-07-15 01:01:27.337 CEST [22783] cefron(at)kontriki LOG: statement:
UPDATE import_temp_2 AS tmp SET status = 3 FROM blacklist_central bl
WHERE tmp.status = 1 AND lower(tmp.email) =
lower(bl.value) AND bl.type = 1
2020-07-15 03:22:01.398 CEST [22783] cefron(at)kontriki LOG: duration:
8434060.530 ms plan:
Query Text: UPDATE import_temp_2 AS tmp SET status = 3 FROM
blacklist_central bl
WHERE tmp.status = 1 AND lower(tmp.email) =
lower(bl.value) AND bl.type = 1
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)

Honestly, I see some differences, but I don't see a real cause that
could make the execution take 2 hours instead of few seconds.
Maybe with these results you or someone in the list is able to find something.

Appreciated for your help,
Eudald

El mar., 14 jul. 2020 a las 23:51, Tom Lane (<tgl(at)sss(dot)pgh(dot)pa(dot)us>) escribió:
>
> =?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald(dot)valcarcel(at)gmail(dot)com> writes:
> > I'm running a docker container that executes a php script running a
> > sequence of queries.
> > One of the queries gets stuck (takes more than 2 hours in execution,
> > active in pg_stat_activity).
> > The query is executed with a JOIN between a FOREIGN TABLE and a local table.
>
> > Executing this query from the psql console takes less than 5 seconds to resolve.
> > If I execute the query from the container created, manually, it
> > resolves as well within 5 seconds.
>
> > Can anyone guide me in a way I can troubleshoot what is causing the
> > query to get stuck?
>
> It's a very good bet that it's something about the query being
> parameterized or not. You could try installing auto_explain to
> compare the plans that are generated.
>
> regards, tom lane