Spring JDBCTemplate + Postgres, same request is very slow

Question

I have this SQL request :

SELECT num.top, num.top / 1.0E7 as value, num.grille_id, 13 as resultat_id FROM (
SELECT SUM(c.top + a.top2 * 900) as top, a.grille_id FROM exe_resultat a 
INNER JOIN exe_resultat_2 c ON c.resultat_id=11 AND c.grille_id = a.grille_id 
WHERE a.resultat_id =13 GROUP BY a.grille_id
UNION ALL 
SELECT a.top2 * 900 as top, a.grille_id FROM exe_resultat a 
LEFT JOIN exe_resultat_2 c ON c. resultat_id=11 AND c.grille_id = a.grille_id 
WHERE a.resultat_id =13 AND c.grille_id IS NULL
UNION ALL 
SELECT c.top, c.grille_id FROM exe_resultat_2 c 
LEFT JOIN exe_resultat a ON a. resultat_id=13 AND a.grille_id = c.grille_id 
WHERE c.resultat_id =11 AND a.grille_id IS NULL) num ORDER BY num.grille_id;

It returns about 15000 lines in less than 1 sec on PGAdmin. When I execute it in my Java Application using Spring JDBC, it takes sometimes less than 1 sec as well but sometimes it takes much more (> 1 minute). There is about 50% chance the request hangs during more than 1 minute. When it happens I can see on PGAdmin server status tab that the request is still executing.

It's not blocked by an other transaction. I tried to increment fetchSize of JDBC. I tried to paginate the request with LIMIT and OFFSET to return only 1000 line each time. I tried with or without prepared statement. I also tried to execute the request in a JUnit test lopping 100 times and it was ok.

It occurs only on the application which is a batch application with several thread (Executor) running sequentially in a specific order doing operation in the database. I suspect this multi-thread environment to be involve in the cause of the problem.

Answer 1

I finally resolve the issue. It was the plan of the request which wasn't the same between the 1st and the 2nd request. Executing vacuum analyze solve the problem. So it was a pure PostgreSQL issue.