Message-ID: <4581593E.8080401@freesurf.fr>
Date: Thu, 14 Dec 2006 15:01:34 +0100
From: Arnaud Lesauvage <thewild@freesurf.fr>
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Ragnar <gnari@hive.is>, Jens Schipkowski <jens.schipkowski@apus.co.at>,
	pgsql-performance@postgresql.org
Subject: Re: Slow update with simple query
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>
In-Reply-To: <8399.1166028385@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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 !