optimizing large query with IN (...)

From: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: optimizing large query with IN (...)
Date: 2004-03-10 03:35:15
Message-ID: 62371.200.174.148.100.1078889715.squirrel@webmail.webnow.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Guys,

I got a Java program to tune. It connects to a 7.4.1 postgresql server
running Linux using JDBC.

The program needs to update a counter on a somewhat large number of
rows, about 1200 on a ~130k rows table. The query is something like
the following:

UPDATE table SET table.par = table.par + 1
WHERE table.key IN ('value1', 'value2', ... , 'value1200' )

This query runs on a transaction (by issuing a call to
setAutoCommit(false)) and a commit() right after the query
is sent to the backend.

The process of committing and updating the values is painfully slow
(no surprises here). Any ideas?

Thanks.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mike Bridge 2004-03-10 03:43:37 High CPU with 7.4.1 after running for about 2 weeks
Previous Message Stephen Robert Norris 2004-03-10 01:32:07 Re: [PERFORM] syslog slowing the database?