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.
