From: Arnaud Lesauvage <thewild@freesurf.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ragnar <gnari@hive.is>
Cc: Jens Schipkowski <jens.schipkowski@apus.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow update with simple query
Date: Thu, 14 Dec 2006 15:01:34 +0100
Message-ID: <4581593E.8080401@freesurf.fr> (raw)
In-Reply-To: <8399.1166028385@sss.pgh.pa.us>
References: <457FDB1E.6040801@freesurf.fr>
<op.tkhy4jpg81rjf6@xjens.apus.local>
<457FF0CD.4050606@freesurf.fr>
<op.tkh10qgw81rjf6@xjens.apus.local>
<4580025D.2040903@freesurf.fr>
<1166021340.6369.103.camel@localhost.localdomain>
<45801A1B.6070200@freesurf.fr>
<8399.1166028385@sss.pgh.pa.us>
Tom Lane a écrit :
> Arnaud Lesauvage <thewild@freesurf.fr> writes:
>> Indeed, the new query does not perform that well :
>
>> "Hash Join (cost=112.75..307504.97 rows=2024869 width=355) (actual time=53.995..246443.811 rows=2020061 loops=1)"
>> ...
>> "Total runtime: 2777844.892 ms"
>
>> I removed all unnecessary indexes on t1 before running the query (I left the index on uid and the multicolumn index containind the updated field).
>> I believe the multicolumn-functional-index computation is taking some time here, isn't it ?
>
> Given that the plan itself only takes 246 sec, there's *something*
> associated with row insertion that's eating the other 2500+ seconds.
> Either index entry computation or constraint checking ...
There is an insert trigger (but here I am only updating the data), and a
multicolumn functional index. That's all I can think of.
I must be missing something, so here is the full table description.
The field I am updating is incluredansstats.
The field I am join on is userinternalid.
CREATE TABLE statistiques.log
(
gid serial NOT NULL,
userinternalid character(32),
ip character varying(255),
browser character varying(255),
fichier character varying(255),
querystring text,
page character varying(255),
useridentity character varying(100),
incluredansstats boolean NOT NULL DEFAULT true,
date character varying,
heure character varying,
dateformatee timestamp without time zone,
sessionid character(32),
sortindex integer,
CONSTRAINT log_pkey PRIMARY KEY (gid)
)
WITHOUT OIDS;
ALTER TABLE statistiques.log OWNER TO postgres;ncluredansstats;
CREATE INDEX idx_page_datemonth_incluredansstats
ON statistiques.log
USING btree
(page, date_trunc('month'::text, dateformatee), incluredansstats);
CREATE INDEX idx_userinternalid
ON statistiques.log
USING btree
(userinternalid);
CREATE INDEX idx_userinternalid_page_datemonth
ON statistiques.log
USING btree
(userinternalid, page, date_trunc('month'::text, dateformatee));
ALTER TABLE statistiques.log
ADD CONSTRAINT log_pkey PRIMARY KEY(gid);
CREATE TRIGGER parse_log_trigger
BEFORE INSERT
ON statistiques.log
FOR EACH ROW
EXECUTE PROCEDURE statistiques.parse_log_trigger();
This was a "one-shot" query, so I don't really mind it being slow, but if you
want I can still troubleshoot it !
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: thewild@freesurf.fr, tgl@sss.pgh.pa.us, gnari@hive.is, jens.schipkowski@apus.co.at
Subject: Re: Slow update with simple query
In-Reply-To: <4581593E.8080401@freesurf.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox