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

From Eudald Valcàrcel Lacasa
Subject Re: Issue executing query from container
Date
Msg-id CANEx+AWCh57b8QggrxKkLoZ5MJtuAYbKAgnTCdNkmL3h0JHJfQ@mail.gmail.com
Whole thread Raw
In response to Re: Issue executing query from container  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Issue executing query from container
List pgsql-general
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



pgsql-general by date:

Previous
From: Naresh Kumar
Date:
Subject: Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Next
From: Karsten Hilbert
Date:
Subject: Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"