Re: Slow update with simple query

[Arnaud Lesauvage <thewild(at)freesurf(dot)fr>]

Tom Lane a écrit :
> Arnaud Lesauvage <thewild(at)freesurf(dot)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 !