Thread: very slow updates
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);
And it takes around 2 minutes !!! (Using ODBC, it also takes around 2
minutes)
I tried to VACUUM the database just before the update, but it doesn't speed
up the process...
If I duplicate my table and I run several times my aplication again, here
are the results :
1 - 11"
2 - 12"
3 - 14"
4 - 15"
5 - 16"
6 - 19"
7 - 1'15"
8 - 1'58"
9 - 1'45"
10- 1'57"
(from this point, it stays around 2 minutes)
Thank you for your help.
Regards,
Xavier Bugaud
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?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
xavier.bugaud@parabolemaurice.com (Xavier Bugaud) wrote in message
news:<A4AAC8CD87A2D511B796004005420B1F64CED3@PMSERVER>...
> 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);
>
Why 1600 updates, when you could do it in one ? Either use no where
clause (if you're really want to update all rows) or use "id>=0 AND
id<1600" as where clause.
Gerhard
> > 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);
>
> Why 1600 updates, when you could do it in one ? Either use no where
> clause (if you're really want to update all rows) or use "id>=0 AND
> id<1600" as where clause.
Of course, I simplified my real problem in this (stupid) query...
In my production environement, I don't update all the rows hence the WHERE
clause.
> Gerhard
--
Xavier Bugaud
> > 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?
> >
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
>
On Fri, Aug 02, 2002 at 03:24:46PM +0400, Xavier Bugaud wrote:
>
> > > 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");"
That's around the whole thing right?
> >
> > > 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
Ay 12 milliseconds per update, you're doing fine. How many updates are you
doing? As someone else pointed out, what you're doing could probably be done
in just one query.
> 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 ?
Postgres will use seq scans if it decides it's better that way. And if your
table is small, it will use that most of the time. Since I don't see the
queries or schema here, you're going to have to post all that to get any
detailed answers.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.