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

Question

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)

What process is causing the timeout? What error message are you getting? Do you just want to stop the timeout? If you want to make the DELETE run faster we'll need some more information. For example, some information about the tables, like how many rows are deleted, how long it takes, the explain plan, and the real query. — Jon Heller
– Jon Heller, Commented May 7, 2013 at 17:38
@jonearles Please see my edit for more details. Yes, I just want to stop the timeout, the request works when I don't have too many line in my table. The number or row deleted depends of the table content (hundreds of thousands sometimes, 0 some other times). This query is the real query, the name of my tables will not give you more informations. — Sébastien BATEZAT
– Sébastien BATEZAT, Commented May 14, 2013 at 13:13
I re-tagged the question based on your edit. You may also want to update the question and title. I don't think your problem is related to PL/SQL. — Jon Heller
– Jon Heller, Commented May 14, 2013 at 18:20

Loek Bergman · Accepted Answer · 2013-05-21 13:23:43Z

1

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.

answered May 21, 2013 at 13:23

Loek Bergman

2,19520 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sébastien BATEZAT Over a year ago

Thanks for your answer that have allowed me to see that I don't need a LEFT JOIN but an INNER JOIN. I have kept my DISTINCT, and I have cut my request in part of 10000 values. I have done that because the problem was not in the time of execution of the jointure, but with the time of the delete.

Loek Bergman Over a year ago

Good to know that it helped. Indeed, INNER JOIN is the equivalent of the where clause I wrote. With the change from Oracle 9i to 10i came a different interpretation of the distinct keyword. It made a whole lot of difference in performance. Since that time am I avoiding it where possible. The way you use distinct is not ambiguous and would perform well in any SQL dialect. Just my 2 cents.

Sportball · Accepted Answer · 2013-05-10 22:34:22Z

0

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

answered May 10, 2013 at 22:34

Sportball

9538 silver badges23 bronze badges

2 Comments

Sportball Over a year ago

If it compiles then you have local variables named true and false in other code not revealed. Doing that is a poor practice.

Sébastien BATEZAT Over a year ago

Ok, thanks for the information. Anyway, that's not answer to my question.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related