UPDATE slow
I have about 11000 rows and this query is much slower than I expected:
update stats set clicks = 123;
stats is basically just: id, clicks; with indexes on each. Right now, the query takes about 10 seconds to execute. It's also on windows 2000/cygwin (if that contributes to slowness). Is it normal for updates like this on that many rows to take a while?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips? Also tried it on MySQL and the update is nearly instantaneous :(.
John
I have about 11000 rows and this query is much slower than I expected:
update stats set clicks = 123;
stats is basically just: id, clicks; with indexes on each. Right now, the query takes about 10 seconds to execute. It's also on windows 2000/cygwin (if that contributes to slowness). Is it normal for updates like this on that many rows to take a while?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Import Notes
Resolved by subject fallback
If this helps :)
db=# explain analyze update stats set clicks = 3344 where link_id=1;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan using idx_link_id on stats
(cost=0.00..149.42 rows=50 width=30) (actual time=0.58..727.45 rows=9994 loops=1)
Index Cond: (link_id = 1)
Total runtime: 8361.93 msec
(3 rows)
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips? Also tried it on MySQL and the update is nearly instantaneous :(.
John
I have about 11000 rows and this query is much slower than I expected:
update stats set clicks = 123;
stats is basically just: id, clicks; with indexes on each. Right now, the query takes about 10 seconds to execute. It's also on windows 2000/cygwin (if that contributes to slowness). Is it normal for updates like this on that many rows to take a while?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Import Notes
Resolved by subject fallback
On Tue, 4 Feb 2003, John Smith wrote:
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips?
Also tried it on MySQL and the update is nearly instantaneous :(.
Hmm, does stats reference another table or is it referenced by another
table?
It has two foreign key references to two other tables. No other tables reference stats though.
John
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
On Tue, 4 Feb 2003, John Smith wrote:
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips?
Also tried it on MySQL and the update is nearly instantaneous :(.
Hmm, does stats reference another table or is it referenced by another
table?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
I should also probably note that neither of the foreign key columns is being updated.
John
It has two foreign key references to two other tables. No other tables reference stats though.
John
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
On Tue, 4 Feb 2003, John Smith wrote:
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips?
Also tried it on MySQL and the update is nearly instantaneous :(.
Hmm, does stats reference another table or is it referenced by another
table?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Import Notes
Resolved by subject fallback
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Tue, 4 Feb 2003 14:37:48 -0800 (PST), John Smith wrote:
Hello John
try:
update stats set clicks=3344 where link_id=1::int8;
I suppose that link_id is an int8 datatype? This bug is already known and will hopefully be fixed in 7.4.
Patric
If this helps :)
db=# explain analyze update stats set clicks = 3344 where link_id=1;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan using idx_link_id on stats
(cost=0.00..149.42 rows=50 width=30) (actual time=0.58..727.45 rows=9994 loops=1)
Index Cond: (link_id = 1)
Total runtime: 8361.93 msec
(3 rows)
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips? Also tried it on MySQL and the update is nearly instantaneous :(.
John
I have about 11000 rows and this query is much slower than I expected:
update stats set clicks = 123;
stats is basically just: id, clicks; with indexes on each. Right now, the query takes about 10 seconds to execute. It's also on windows 2000/cygwin (if that
contributes to slowness). Is it normal for updates like this on that many rows to take a while?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
PGP Public Key Fingerprint: 2636 F26E F523 7D62 4377 D206 7C68 06BB
-----BEGIN PGP SIGNATURE-----
Version: PGPsdk version 1.7.1 (C) 1997-1999 Network Associates, Inc. and its affiliated companies.
iQA+AwUBPkA2DXxoBrvMu8qQEQKJcwCY7c1OjuvS/RIQeN+q0Z4dWIHp/gCdFEAR
VRTPdgIp8C0PM6Tv3a2NqgA=
=4QYn
-----END PGP SIGNATURE-----
On Tue, 4 Feb 2003, John Smith wrote:
I should also probably note that neither of the foreign key columns is being updated.
It doesn't matter, unfortunately. It currently checks even in that case
due to ON * SET DEFAULT having a failure case if you don't check when no
keys are changed (if you remove the default - and you can't necessarily
check this beforehand either if the defaults are not stable). Among the
various changes I've been working with is making it only do the check if
one of the set default cases is given or if the key changed.
No difference speed-wise:(. link_id is int4 (as is clicks). I also tried:
update stats set clicks=3344::int4;
and it still takes about the same amount of time (5-10 secs).
John
Patric Bechtel <bechtel@ipcon.de> wrote:-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Tue, 4 Feb 2003 14:37:48 -0800 (PST), John Smith wrote:
Hello John
try:
update stats set clicks=3344 where link_id=1::int8;
I suppose that link_id is an int8 datatype? This bug is already known and will hopefully be fixed in 7.4.
Patric
If this helps :)
db=# explain analyze update stats set clicks = 3344 where link_id=1;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan using idx_link_id on stats
(cost=0.00..149.42 rows=50 width=30) (actual time=0.58..727.45 rows=9994 loops=1)
Index Cond: (link_id = 1)
Total runtime: 8361.93 msec
(3 rows)
Just tried it on Linux - still takes 5-10 seconds. Any speed-up tips? Also tried it on MySQL and the update is nearly instantaneous :(.
John
I have about 11000 rows and this query is much slower than I expected:
update stats set clicks = 123;
stats is basically just: id, clicks; with indexes on each. Right now, the query takes about 10 seconds to execute. It's also on windows 2000/cygwin (if that
contributes to slowness). Is it normal for updates like this on that many rows to take a while?
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
PGP Public Key Fingerprint: 2636 F26E F523 7D62 4377 D206 7C68 06BB
-----BEGIN PGP SIGNATURE-----
Version: PGPsdk version 1.7.1 (C) 1997-1999 Network Associates, Inc. and its affiliated companies.
iQA+AwUBPkA2DXxoBrvMu8qQEQKJcwCY7c1OjuvS/RIQeN+q0Z4dWIHp/gCdFEAR
VRTPdgIp8C0PM6Tv3a2NqgA=
=4QYn
-----END PGP SIGNATURE-----
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Import Notes
Resolved by subject fallback
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
I should also probably note that neither of the foreign key columns is being updated.
It doesn't matter, unfortunately. It currently checks even in that case
due to ON * SET DEFAULT having a failure case if you don't check when no
keys are changed (if you remove the default - and you can't necessarily
check this beforehand either if the defaults are not stable). Among the
various changes I've been working with is making it only do the check if
one of the set default cases is given or if the key changed.
I've actually got most of that done as part of the other patches I've been
working on. I could probably try to do the same to 7.3 (as opposed to
7.4 devel) and make a separate patch out of it.
Think that makes sense ;). However, I just dropped all the foreign key constraints on stats and there wasn't any speed increase in the query. I've also tried dropping the index on clicks (no speed-up there either).
John
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
I should also probably note that neither of the foreign key columns is being updated.
It doesn't matter, unfortunately. It currently checks even in that case
due to ON * SET DEFAULT having a failure case if you don't check when no
keys are changed (if you remove the default - and you can't necessarily
check this beforehand either if the defaults are not stable). Among the
various changes I've been working with is making it only do the check if
one of the set default cases is given or if the key changed.
I've actually got most of that done as part of the other patches I've been
working on. I could probably try to do the same to 7.3 (as opposed to
7.4 devel) and make a separate patch out of it.
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
On Tue, 4 Feb 2003, John Smith wrote:
Think that makes sense ;). However, I just dropped all the foreign key
constraints on stats and there wasn't any speed increase in the query.
I've also tried dropping the index on clicks (no speed-up there
either).
Can you duplicate it with a new table?
Something like:
create table foo as select * from stats;
update foo set clicks=3344;
Might we see your table schema? This and the table(s) with the foreign
keys as well?
On Tue, 4 Feb 2003, John Smith wrote:
Show quoted text
No difference speed-wise:(. link_id is int4 (as is clicks). I also tried:
update stats set clicks=3344::int4;
and it still takes about the same amount of time (5-10 secs).
John
That works - updates on foo take about 1.4 seconds. I dropped all the indexes and fk's on stats and updates there take about 2.8 seconds. These are on the cygwin machine.
Looking at the tables, foo doesn't have any modifiers for the columns (stats does - not null default 0).
John
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:On Tue, 4 Feb 2003, John Smith wrote:
Think that makes sense ;). However, I just dropped all the foreign key
constraints on stats and there wasn't any speed increase in the query.
I've also tried dropping the index on clicks (no speed-up there
either).
Can you duplicate it with a new table?
Something like:
create table foo as select * from stats;
update foo set clicks=3344;
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the indexes and fk's on stats and updates there take about 2.8 seconds. These are on the cygwin machine.
Looking at the tables, foo doesn't have any modifiers for the columns (stats does - not null default 0).
John
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:On Tue, 4 Feb 2003, John Smith wrote:Think that makes sense ;). However, I just dropped all the foreign key
constraints on stats and there wasn't any speed increase in the query.
I've also tried dropping the index on clicks (no speed-up there
either).Can you duplicate it with a new table?
Something like:
create table foo as select * from stats;
update foo set clicks=3344;
Is this a table that got a lot of updates en masse? If it was regularly
vacuumed but you still had a problem, it might be index bloat. Keep an
eye on it, and if the table starts to get slow, try reindexing the indexes
on that table and see if that happens.
The "fix" for this problem is to crank up max fsm settings, and run vacuum
more often, but that doesn't always actually fix things.
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the
indexes and fk's on stats and updates there take about 2.8 seconds.
These are on the cygwin machine.
The 2.8 seconds is on stats after dropping the fks and indexes? But
it didn't help on the linux box?
Have you done a vacuum/reindex recently?
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the
indexes and fk's on stats and updates there take about 2.8 seconds.
These are on the cygwin machine.The 2.8 seconds is on stats after dropping the fks and indexes? But
it didn't help on the linux box?
I tried this earlier on just a plain:
create table testme ( id integer unique, clicks integer unique );
This is my explain:
desticorp=> explain analyze select count(1) from testme;
NOTICE: QUERY PLAN:
Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=78.52..78.52 rows=1 loops=1)
-> Seq Scan on testme (cost=0.00..20.00 rows=1000 width=0) (actual time=0.11..51.74 rows=11999 loops=1)
Total runtime: 78.67 msec
EXPLAIN
desticorp=> explain analyze update testme set clicks = clicks + 123;
NOTICE: QUERY PLAN:
Seq Scan on testme (cost=0.00..20.00 rows=1000 width=14) (actual time=0.03..256.21 rows=11999 loops=1)
Total runtime: 2060.41 msec
EXPLAIN
As you can see this took 2 seconds after already been given the chance to cache
the table. It's also possible to see that I used 12000 rows in my table and
that the sequential scan part of the operation is a helluva lot slower when
writing.
This is on a dual P-III 550MHz system. Memory settings probably aren't tuned
too much though I don't think that would impact too much on this quick
test. Although loaded and usually noticable delays in window refresh when
flicking through my screens this system seems to have a very low CPU
utilisation and plenty of memory usable (for a change). Although I am wondering
what I've run in the last couple of weeks that's pushed me to use 150MB of swap
(768MB physical so not an insignificant amount).
Therefore John's 2.8s seems a reasonable time to me.
--
Nigel J. Andrews
On Wed, 5 Feb 2003, Nigel J. Andrews wrote:
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the
indexes and fk's on stats and updates there take about 2.8 seconds.
These are on the cygwin machine.The 2.8 seconds is on stats after dropping the fks and indexes? But
it didn't help on the linux box?desticorp=> explain analyze update testme set clicks = clicks + 123;
NOTICE: QUERY PLAN:
Seq Scan on testme (cost=0.00..20.00 rows=1000 width=14) (actual time=0.03..256.21 rows=11999 loops=1)
Total runtime: 2060.41 msecEXPLAIN
As you can see this took 2 seconds after already been given the chance to cache
the table. It's also possible to see that I used 12000 rows in my table and
that the sequential scan part of the operation is a helluva lot slower when
writing.This is on a dual P-III 550MHz system. Memory settings probably aren't tuned
too much though I don't think that would impact too much on this quick
test. Although loaded and usually noticable delays in window refresh when
flicking through my screens this system seems to have a very low CPU
utilisation and plenty of memory usable (for a change). Although I am wondering
what I've run in the last couple of weeks that's pushed me to use 150MB of swap
(768MB physical so not an insignificant amount).Therefore John's 2.8s seems a reasonable time to me.
Yeah, but I thought he'd said that on the linux box, even after dropping
indexes and fks it was taking 5-10 seconds.
I'm also a bit confused because I'm not sure he's getting 2.8 seconds to
update all the records or just a single record.
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Wed, 5 Feb 2003, Nigel J. Andrews wrote:
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the
indexes and fk's on stats and updates there take about 2.8 seconds.
These are on the cygwin machine.The 2.8 seconds is on stats after dropping the fks and indexes? But
it didn't help on the linux box?...
Yeah, but I thought he'd said that on the linux box, even after dropping
indexes and fks it was taking 5-10 seconds.
You miss remembered :)
I'm also a bit confused because I'm not sure he's getting 2.8 seconds to
update all the records or just a single record.
I'm pretty sure the command originally quoted was an unconstrained update
setting a constant value, i.e. all the rows. I had to change my test because
I'd setup unique indexes so couldn't do the constant value bit.
--
Nigel Andrews
On Wed, 5 Feb 2003, Nigel J. Andrews wrote:
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Wed, 5 Feb 2003, Nigel J. Andrews wrote:
On Tue, 4 Feb 2003, Stephan Szabo wrote:
On Tue, 4 Feb 2003, John Smith wrote:
That works - updates on foo take about 1.4 seconds. I dropped all the
indexes and fk's on stats and updates there take about 2.8 seconds.
These are on the cygwin machine.The 2.8 seconds is on stats after dropping the fks and indexes? But
it didn't help on the linux box?...
Yeah, but I thought he'd said that on the linux box, even after dropping
indexes and fks it was taking 5-10 seconds.You miss remembered :)
In any case that's 3x slower than my development box which is not terribly
powerful and not set up as a database server for real using pretty much
all default configuration settings with the database on the same partition
as / (/usr, etc...).
I'm also a bit confused because I'm not sure he's getting 2.8 seconds to
update all the records or just a single record.I'm pretty sure the command originally quoted was an unconstrained update
setting a constant value, i.e. all the rows. I had to change my test because
I'd setup unique indexes so couldn't do the constant value bit.
That's what the first message was, but his first followup with an explain
analyze output used:
explain analyze update stats set clicks = 3344 where link_id=1;
