Re: UPDATE slow [Viruschecked] - Mailing list pgsql-general

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