Received: from primaire.intelcom.fr (primaire.intelcom.fr [194.250.33.1]) by hub.org (8.10.1/8.10.1) with ESMTP id e7TC9ZC63598 for ; Tue, 29 Aug 2000 08:09:35 -0400 (EDT) Received: from volvo.intelcom.fr ([192.168.160.20]) by primaire.intelcom.fr (8.8.8+Sun/8.8.8) with SMTP id OAA09927 for ; Tue, 29 Aug 2000 14:09:20 +0200 (MET DST) Received: from intelcom.fr by volvo.intelcom.fr (SMI-8.6/SMI-SVR4) id OAA23508; Tue, 29 Aug 2000 14:09:22 +0200 Message-ID: <39ABA7F2.6180FFC7@intelcom.fr> Date: Tue, 29 Aug 2000 14:09:22 +0200 From: Jerome Raupach X-Mailer: Mozilla 4.75 [en] (X11; U; SunOS 5.5 sun4d) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: performance on insert/update Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/320 I have a big problem of performance, please help me. it is my work : first : COPY table1 FROM 'file' -> 43s, INSERT INTO table2 -> 34s, UPDATE table2 -> 1mn 29s ( =2m 46s : OK) second : COPY table1 FROM 'same file' -> 1m 10s, INSERT INTO table2 -> 2m 14s, UPDATE table2 -> 5mn 20s ( =8m 44s ) third : COPY table1 FROM 'same file' -> 1m, INSERT INTO table2 -> 2m 20s, UPDATE table2 -> 7mn 30s ( =10m 50s ) ... EXPLAIN UPDATE table2 (first, second and third) : Merge Join (cost=0.00..8425.71 rows=1 width=102) -> Index Scan using index2 on table2(cost=0.00..8320.70 rows=1000 width=42) SubPlan -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) -> Index Scan using index2 on table2 (cost=0.00..60.00 rows=1000 width=60) SubPlan -> Index Scan using index2 on table2 (cost=0.00..10.28 rows=1 width=42) SubPlan -> Aggregate (cost=8.26..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) -> Index Scan using index2 on table2 (cost=0.00..10.28 rows=1 width=42) SubPlan -> Aggregate (cost=8.26..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=4) -> Index Scan using table1_pkey on table1 (cost=0.00..8.26 rows=1 width=46) ------------------------------------------------------------------------------ .../bin/postmaster -d2 -D .../data -p 19118 -i -N64 -B1024 CREATE TABLE table1 (f1 VARCHAR(20) NOT NULL, date_conn DATETIME NOT NULL, time INT4, PRIMARY KEY(f1, date_conn)); CREATE INDEX index1 ON table1( f1, date_conn ) ; CREATE TABLE table2 (f1 VARCHAR(20),nb INT4,time INT4,tmc INT4,date DATE); CREATE INDEX index2 ON table2( f1, date ) ; CREATE VIEW view1 AS SELECT f1, ( SELECT COUNT(*) FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date ) AS nb, ( SELECT SUM(table1.time) FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date ) AS time, date FROM table2 WHERE exists ( SELECT f1, date_conn FROM table1 WHERE table1.f1=table2.f1 AND table1.date_conn::date=table2.date) ; CREATE INDEX index_view1 ON view1( f1, date ) ; ------------------------------------------------------------------------------ I have a file 'file' : 20162 tuples. (f1/date_conn/time) "COPY table_brut FROM 'file' USING DELIMITERS '/'; INSERT INTO table2 SELECT DISTINCT f1, 0, 0, 0, date_conn::date FROM table1 WHERE not exists ( SELECT table2.f1, table2.date FROM table2, table1 WHERE table2.f1=table1.f1 AND table2.date=table1.date_conn::date ); UPDATE table2 SET nb=nb+( SELECT nb FROM view1 WHERE view1.f1=table2.f1 AND view1.date=table2.date ), temps=time+( SELECT time FROM view1 WHERE view1.f1=table2.f1 AND view1.date=table2.date ) WHERE table2.f1=view1.f1 AND table2.date=view1.date ; UPDATE table2 SET tmc=time/nb; DELETE FROM table1; ------------------------------------------------------------------------------ Sorry for my english, and Thanks in advance. Jerome.