Kai Hessing wrote:
Deadlock means it hangs up and doesn't terminate through timeout.
The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again ....
It is possible for a query to run for many days, and still finish. This classifies as slow, not hung. The difference is important in troubleshooting to determine the cause.
Also EXPLAIN output, and information regarding the number of rows in each table, and the number of rows matching veraid = 2 and veraid = 34 might help.Explain produces the same problem. It just takes forever...
Did you try EXPLAIN, or EXPLAIN ANALYZE? The former only does the planning, the latter actually materializes the result in order to get actual timings, and will therefore take at least as long as the query itself.
While the query is running, how much CPU is the process consuming, and what does vmstat show for disk and swap I/O?~80% CPU power. Disk usage is not noticable.
Can you attach with a debugger and see exactly what's going on? If not we'd need that self contained test case to reproduce the problem.
Joe
