Re: 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?
> >