Thread: Re: UPDATE slow [Viruschecked]

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 nearl y 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
VRTPdgIp 8C0PM6Tv3a2NqgA=
=4QYn
-----END PGP SIGNATURE-----

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:

>
> 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

Sure!

##############################################
CREATE TABLE referrers(
 id serial NOT NULL UNIQUE PRIMARY KEY,
 domain varchar(255) NOT NULL UNIQUE,
 added timestamp without time zone DEFAULT now() NOT NULL);

CREATE UNIQUE INDEX IDX_referrers_id ON referrers (id);
CREATE UNIQUE INDEX IDX_referrers_email ON referrers (domain);
CREATE INDEX IDX_referrers_added ON referrers (added);

CREATE TABLE users(
 id serial NOT NULL UNIQUE PRIMARY KEY,
 email varchar(255),
 first_name varchar(30),
 last_name varchar(50),
 password varchar(16));

 CREATE UNIQUE INDEX IDX_users_id ON users (id);
 CREATE INDEX IDX_users_email ON users (email);
 CREATE INDEX IDX_users_password ON users (password);

CREATE TABLE links(
 id serial NOT NULL UNIQUE PRIMARY KEY,
 name varchar(255),
 added timestamp without time zone DEFAULT now() NOT NULL,
 url varchar(255),
 user_id int4 NOT NULL,
 FOREIGN KEY (user_id) REFERENCES users (id));

CREATE UNIQUE INDEX IDX_links_id ON links (id);
CREATE INDEX IDX_links_name ON links (name);
CREATE INDEX IDX_links_added ON links (added);
CREATE INDEX IDX_links_user_id ON links (user_id);

CREATE TABLE stats(
 added timestamp without time zone DEFAULT now() NOT NULL,
 clicks int4 DEFAULT 0 NOT NULL,
 link_id int4 NOT NULL,
 referrer_id int4 NOT NULL,
 FOREIGN KEY (link_id) REFERENCES links (id) on delete cascade on update cascade,
 FOREIGN KEY (referrer_id) REFERENCES referrers (id) on delete cascade on update cascade);

CREATE INDEX IDX_stats_added ON stats (added);
CREATE INDEX IDX_stats_clicks ON stats (clicks);
CREATE INDEX IDX_stats_link_id ON stats (link_id);
CREATE INDEX IDX_stats_referrer_id ON stats (referrer_id);

 "scott.marlowe" <scott.marlowe@ihs.com> wrote:

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:

>
> 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