Thread: performance on insert/update

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                  ->
1mn29s ( =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
ANDview1.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.

Jerome Raupach <jraupach@intelcom.fr> writes:
> I have a big problem of performance, please help me.

You could code the update so it only evaluates the view once,
rather than twice per table2 row as you now have it:

UPDATE table2 SETnb=table2.nb+view1.nb,time=table2.time+view1.time
FROM view1
WHERE view1.f1=table2.f1 and view1.date=table2.date;

Subselects are nice, but they're not cheap.  Joins are faster.

The view itself also seems to be pretty inefficiently coded,
although I'm not sure you can do much better given that GROUP BY
doesn't really work in views right now.  Hopefully by 7.2 there
will be a better way to do the view.
        regards, tom lane