From: Tom Lane <tgl@sss.pgh.pa.us>
To: Novak Ivan <ivan.novak@adcubum.com>
cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>,
        =?utf-8?B?S8O8bWluIEFudG9uaW8=?= <antonio.kuemin@adcubum.com>
Subject: Re: Create temp table query hangs
In-reply-to: <F669A04E-CF43-4A07-B4C9-7A210CEAF19E@adcubum.com>
References: <F669A04E-CF43-4A07-B4C9-7A210CEAF19E@adcubum.com>
Comments: In-reply-to Novak Ivan <ivan.novak@adcubum.com>
	message dated "Tue, 13 Apr 2021 18:26:02 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3378152.1618344138.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 13 Apr 2021 16:02:18 -0400
Message-ID: <3378153.1618344138@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3378153.1618344138%40sss.pgh.pa.us>
Precedence: bulk

Novak Ivan <ivan.novak@adcubum.com> writes:
>   *   a query for creating temporary table on target schema is hanging f=
orever and we are out of ideas why.

Looking at wait_event as well as wait_event_type in pg_stat_activity
might give some more clues.

> When we try to create a (temporary) table with one statement, and that q=
uery takes forever (even after >24h the query is still there). In the Post=
gres Database we see three identical active PIDs for the same query (with =
same starttimestamp). But we only sent the statement once to the DB. See s=
creenshot below.

I think this is just a parallelized query with two worker processes.

Were the query's source tables freshly created or freshly populated?
If so, you might need to issue ANALYZE commands on them before you
start the query itself, to make sure the query planner has valid
statistics to work with.  It's possible that the problem is just a
very dumb choice of query plan due to lack of stats.

Have you tried comparing EXPLAIN output for this query across the
scenarios where it works well and where it doesn't?  If it is a
bad-plan problem then there'd be obvious differences in the shape
of the plan.

			regards, tom lane