Re: Issue executing query from container - Mailing list pgsql-general

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@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@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@kontriki LOG:  duration: 0.049 ms
2020-07-15 01:01:27.337 CEST [22783] cefron@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@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@sss.pgh.pa.us>) escribió:
>
> =?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald.valcarcel@gmail.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