Re: very slow updates

might it be possible that "id" is not type integer/int4, e.g. numeric or
int8?

try this query:
rs2.executeUpdate("UPDATE my_table SET my_date=now() WHERE id='"+i+"'");


----- Original Message -----
From: "Xavier Bugaud" <xavier.bugaud@parabolemaurice.com>
To: "'Martijn van Oosterhout'" <kleptog@svana.org>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, August 02, 2002 1:24 PM
Subject: Re: [GENERAL] very slow updates


>
> > > 1. I tried with and without transctions : same result. In the tests I
> make
> > > right now, I have disable transactions.
> >
> > Same result, really?
>
> let's say quite same result (maybe 1 or 2 seconds difference. it's hard to
> say because the time elapsed is always increasing)...
> either using : "con.setAutoCommit(false); (...) con.commit();"
> or : "st.executeUpdate("BEGIN"); (...) st.executeUpdate("COMMIT");"
>
> >
> > > 2. yes, a unique index.
> > >
> > > Each time I run a "VACUUM FULL", the process is very fast again for
6-7
> > > times (10-15s). After that, it takes again about 2-3 minutes...
> > > When I only run a "VACUUM" (not FULL), it doesn't make any difference.
> >
> > Ok, show us the EXPLAIN ANALYZE both before and after the problem. You
> > arerunning ANAYSE, right?
>
> I set shared_buffers to 1024. Now, the duration of the updates is
increasing
> regularly (there is no more 'jump' from 20secs to 2 minutes).
> But the duration still increase with the time...
>
> * Just after a "VACUUM FULL ANALYSE" (duration = 8'):
> Seq Scan on messages_service  (cost=0.00..67.46 rows=1 width=188) (actual
> time=3.21..3.21 rows=0 loops=1)
> Total runtime: 3.35 msec
>
> * After running the application 20 times (duration = 21'):
> Seq Scan on messages_service  (cost=0.00..88.46 rows=1 width=188) (actual
> time=12.18..12.18 rows=0 loops=1)
> Total runtime: 12.31 msec
>
>
> As shown on these results, postgres doesn't use indexes... I create
another
> table on another base with 2000 rows, and this time, it uses indexes (and

> it's damn fast !).
> So I guess I find where is my problem : postgres use "Seq Scan" instead of
> "Index Scan"... But why ?
>
>
> >
> > >
> > > On Thu, Aug 01, 2002 at 09:41:39AM +0400, Xavier Bugaud wrote:
> > > > Hi,
> > > >
> > > > I'm running PostgreSQL 7.2.1 with a redhat 7.2 box (PIII 800 / 256MB
> RAM /
> > > > RAID1 IDE : promise TX2000).
> > > > I'm new to Postgres, so perhaps I missed something...
> > > >
> > > > Here is the point :
> > > > Using JDBC, I update one column for all the 1600 rows of a table (10
> > > > columns) : I'm running 1600 times a query like this :
> > > > for (i=0 ; i<1600 ; i++)
> > > >   rs2.executeUpdate("UPDATE my_table SET my_date=now() WHERE
id="+i);
> > >
> > > 1. Are you using transactions?
> > > 2. Do you have an index on id?
> > >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>