Re: Create temp table query hangs

Novak Ivan <ivan(dot)novak(at)adcubum(dot)com> writes:
> * a query for creating temporary table on target schema is hanging forever 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 query takes forever (even after >24h the query is still there). In the Postgres 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 screenshot 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