Peter Hinse <loco@d0pefish.de> writes:
> More info: the statement is an INSERT with some huge subselects, running
> every night on a PGSQL 8.3.6 on CentOS 4.7 x86_64. In 97% of all
> occasions, the job terminates in about 1-2 minutes - however, sometimes
> it just hangs. If terminated with kill <pid> and restarted, it always
> terminates.
Define "huge" --- you mean a lot of relations in the query? If you have
enough to trigger GEQO optimization, it could be that it's sometimes
picking a bad plan. You might try raising the geqo threshold to more
relations than that; or if this results in unacceptably long planning
time, increase geqo_effort instead.
regards, tom lane