CODEWITHSUNDEEP
javaperformancepostgresqltimeoutplpgsql
Sébastien BATEZAT
Sébastien BATEZAT

Reputation: 2569

How to improve postgresql request that throw a "statement timeout"

This request throw a statement timeout with postgresql.

DELETE FROM my_table where my_table.id IN (
   SELECT DISTINCT b.id 
   FROM my_table a
   LEFT JOIN my_table b
   on b.rowA = a.rowA and b.rowB = true
   WHERE a.rowB = false
)

For some reasons, I can't augment my timeout on postgresql. So, I need to improve my request. How to improve it ? Maybe by not using IN? How to do that?

Thanks per advance for your help.

EDIT with more informations :

I'm in a JAVA batch and the error message I have is the following :

Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)

Upvotes: 1

Views: 2300

Answers (2)

Loek Bergman
Loek Bergman

Reputation: 2195

I would remove the distinct and the left join. 

delete from my_table where my_table.id in (select b.id from tblA a, tblB b 
where a.rowB = false and b.rowa = a.rowa and b.rowb = true);

The main cause of your problem is the left join. Why should you use a left join on table a and base the result on the ids of table b? If you would have used the ids of table a, then would that make sense. A left join from the point of view of table a will return all ids from table a, even when there is no match in table b. That is not what you want, because it can be a huge amount of data. You want a subset of table a and table b. Hence should you not ask all data from a (left join) without using it. Distinct can also create a performance issue. Only use it, when it has added value. I don´t see added value here.

Upvotes: 1

Michael O'Neill
Michael O'Neill

Reputation: 954

You cannot use true and false (i.e., boolean literals) in an SQL context, even from with PL/SQL.

Upvotes: 0

Related Questions