Re: very slow updates
От | Xavier Bugaud |
---|---|
Тема | Re: very slow updates |
Дата | |
Msg-id | A4AAC8CD87A2D511B796004005420B1F64CEE2@PMSERVER обсуждение исходный текст |
Ответ на | very slow updates (Xavier Bugaud <xavier.bugaud@parabolemaurice.com>) |
Ответы |
Re: very slow updates
|
Список | pgsql-general |
> > 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? > >
В списке pgsql-general по дате отправления: